Context variables

[vincent5]

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.

[Anonymous]

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]

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

[vincent5]

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]

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]

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]

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

[vincent5]

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]

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

example:-

substing(context.abc,1,4)

[Anonymous]

@vincent

   Please refer the places where you are having issues.

Warm Regards,

Nikhil Thampi