topic [resolved] Change where clause in a lookup with tPostgresqlInput in Talend Studio

[resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Thu, 09 Jun 2016 13:32:38 GMT

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

Re: [resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Mon, 13 Jun 2016 07:43:01 GMT

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

Re: [resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Mon, 13 Jun 2016 08:16:06 GMT

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.

Re: [resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Mon, 13 Jun 2016 12:53:36 GMT

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.

Re: [resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Fri, 05 Aug 2016 08:25:23 GMT

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

Re: [resolved] Change where clause in a lookup with tPostgresqlInput

Anonymous — Fri, 05 Aug 2016 12:09:58 GMT

Hi,

can you give more details about your query please ?