Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vincent5
Contributor
Contributor

Context variables

Hi,

I'm using Talend V6.4.1 Integration with an Informix database.
I would like to concatenate 2 context variables in a tELTInformixMap.
For example :
My first variable is equal to 'CM0' (context.CM0)
My second variable is equal to '87' (context.CAISSE)
I would like to get 'CM087' to insert it in an informix table.
I tried all kinds of writing but none give me the expected result.
The last try is the following script: 'context.CM0 + context.CAISSE'
Which gives as result: 'CM0+87' (incorrect)
  Thanks for your help.

 

Labels (2)
13 Replies
Anonymous
Not applicable

Hi,

 

    Please remove single quotes. Just try context.CM0 + context.CAISSE

 

     Since it is a variable, you should not add any quotes.

 

    If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

 

Warm Regards,

 

Nikhil Thampi

manodwhb
Champion II
Champion II

@vincent,if you were using a tmap in your data flow ,you can use in tmap below way.in tjavarow also you can do .0683p000009M1G9.png

vincent5
Contributor
Contributor
Author

Hi,

The job crashes with this message:

java.sql.SQLException: Column (cm0) not found in any table in the query (or SLV is undefined).

Regards,

Vincent

Anonymous
Not applicable

Hi,

 

Keep in mind that when you're using ELT components you're basically writing SQL code instead of Java. 

 

You'll need to use the CONCAT(expr1,expr2) function as defined in informix SQL. In your example :

 

CONCAT(context.CM0,context.CAISSE)

 

The mapper will automatically take care of escaping your context variables.

 

Let me know it this works

 

Kind regards,

 

Michael

Anonymous
Not applicable

Hi Vincent,

 

    If you dont mind, could you publish the entire SQL you are using in the Informix? A sample query is also fine. Just want to make sure that you are using the SQL and the context variables in the right way.

 

Warm Regards,

 

Nikhil Thampi

vincent5
Contributor
Contributor
Author

Hi,

I do not know how it converted the variables but I have a crash when inserting the data in database:

 

Exception in component agELTInformixOutput_2 (CAC07_TB_1_101_Alim_tmp_collecte_AC_CCOL)
java.sql.SQLException: Column (cm0) not found in any table in the query (or SLV is undefined).
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3770)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2576)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2492)
    at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1360)
    at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:320)
    at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:300)
    at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:170)
    at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:6523)
    at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2334)
    at

 

 

0683p000009M1GN.jpg

vincent5
Contributor
Contributor
Author

Hi,

The sql code with the concat gives this:

 

"SELECT
distinct substr('" +context.DATE_TRT+ "',1,4)-1, CONCAT (" +context.CM0+ ", " +context.CAISSE+ "), opt.nsirent, opt.lsirent, opt.noc, nvl(dlu.ndpt_lin_unq,'" +context.CAISSE+ "'), opt.npdt, ident.nent, ident.neta, adh.badhcol, adh.fadhcol, opt.noptcol, opt.voptcol, case when ( to_char(adh.fadhcol,'%Y') >= substr('" +context.DATE_TRT+ "',1,4)) then 1 else 0 end, case when ( to_char(adh.badhcol,'%Y') = substr('" +context.DATE_TRT+ "',1,4)-1) then 1 else 0 end, case when ( to_char(adh.fadhcol,'%Y') = substr('" +context.DATE_TRT+ "',1,4)-1) then 1 else 0 end, '" +context.DATE_TRT+ "'
FROM
 cac87:acgestopt opt INNER JOIN  cac87:acgestadh adh ON(  adh.nordcle = opt.nordcle )
 INNER JOIN  cac87:acgestident ident ON(  ident.nidtadhcol = adh.nidtadhcol  AND  ident.norgrsp = adh.norgrsp  )
 LEFT OUTER JOIN  com87:dlu dlu ON(  dlu.nide = ident.nent  )
WHERE
  opt.npdt IS NOT NULL
 AND (to_char(adh.badhcol,'%Y') >= substr('" +context.DATE_TRT+ "',1,4)-1 OR  to_char(adh.fadhcol,'%Y') >=  substr('" +context.DATE_TRT+ "',1,4)-1 )"

manodwhb
Champion II
Champion II

@vincent,i do not understand why you are giving contexts in single quotes,quotes are not needed.

 

example:-

substing(context.abc,1,4)

Anonymous
Not applicable

@vincent

 

   Please refer the places where you are having issues.

0683p000009M0qd.png

 

 

Warm Regards,

 

Nikhil Thampi