[resolved] Change where clause in a lookup with tPostgresqlInput

[Anonymous]

Hello everyone.

Talend   = TOS 6.1.1
OS        = Mac Os El Capitan

My job is

tConnection --> tFileInputDelimited--> tMap (few lookups with tPostgesqlInput) --> tPostgresqlOutput --> tCommit

In one of the lookup, i want to select data with a value from another lookup.
It works because i've debugged values and see the good values.
My problem is that i don't want to use all the value received but a part of it :

SELECT \n  ref_invoice FROM \""+
context.pBddSchema+"\".\"invoice\"\n WHERE ref_invoice ILIKE substring(my_column from 1 for 4)||'-' ||to_char(now(), 'YYYY')||'-%' \nORDER BY ref_invoice DESC\nLIMIT 1"

my_column is NOT the column in the schema but the key value received from the lookup Tmap.
So query will be :

SELECT \n  ref_invoice FROM \""+
context.pBddSchema+"\".\"invoice\"\n WHERE ref_invoice ILIKE substring('FRGF-2016-06-12345' from 1 for 4)||'-' ||to_char(now(), 'YYYY')||'-%' \nORDER BY ref_invoice DESC\nLIMIT 1"

I don't have found a solution to replace the my_column parameter with the value from the tMap.
Any idea ?
For the moment, the query retreive all the table's data but i just want for exemple the FRGF ones.

In another ETL and with Oracle, i've just to write :1 to tell it to replace the parameter 1 with my value

[Anonymous]

Hi,

nobody to help me ?
I've tried the tSetGlobalVar and it doesn't work in the tPostgresqlInput. The value is good in the tMap but it fails in the tPostgresqlInput.
"SELECT \n  ref_invoice FROM \""+
context.pBddSchema+"\".\"invoice\"\n WHERE ref_invoice ILIKE ((String)globalMap.get("PrefixOrg"))||'-' ||to_char(now(), 'YYYY')||'-%' \nORDER BY ref_invoice DESC\nLIMIT 1"
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
    The operator * is undefined for the argument type(s) java.lang.String, java.lang.String
    Syntax error on token "PrefixOrg", * expected

[Anonymous]

I've no error writing the query like this :

"SELECT \n  ref_invoice FROM \""+
context.pBddSchema+"\".\"invoice\"\n WHERE ref_invoice ILIKE '" + ((String)globalMap.get("PrefixOrg")) + "' ||'-' ||to_char(now(), 'YYYY')||'-%' \nORDER BY ref_invoice DESC\nLIMIT 1"

But it retrieve nothing.

[Anonymous · Accepted solution]

Found it !

at the tMap for the lookup, choose "Reload at each row".
Then a globalMap expression appears. Put your value with a global map variable as :
=> for the expression, the column with the value
=> for the key globalmap, the name of the variable (here myKey for the exemple)
In the query, you can use it with ((String)globalMap.get("myKey")) and it works.

Thanks to myself.

[Anonymous]

hai Carlos.Lopes   I am facing syntax error ((String)globalMap.get("myKey"))  whaile using tis syntax.Can you help me

[Anonymous]

Hi,

can you give more details about your query please ?