topic Re: tMap lookup inner join with DB where clause using globalMap.get() not working in Talend Studio

tMap lookup inner join with DB where clause using globalMap.get() not working

synfield — Wed, 19 Sep 2018 23:11:29 GMT

Hi,

I've been trying to do a DB table lookup based on the ID that is returning from a REST service, to avoid duplication of records, in jobs already run.

However, I can't get the lookup working the REST record has an ID "code" and I look this up in the DB table using "SELECT...WHERE = '" + (String)globalMap.get("row22.code") + "' etc.. to see if this record already exists in the DB table to avoid duplication, but is not working. The (String)globalMap.get("row22.code") comes from the tFlowToIterate ----> tIterateToFlow components as below;

The SQL Select statement screenshot, is taken from the Query textbox in the tPostgresqlInput component

I can just do a SELECT all, and load table in memory to do a lookup, which works, but I don't think that's very efficient!

Cheers,

Synfield.

Re: tMap lookup inner join with DB where clause using globalMap.get() not working

fdenis — Thu, 20 Sep 2018 08:25:28 GMT

in you tMap select "reload for each row"
add lockup key using +
and use this globlaMap key in your sql query

Re: tMap lookup inner join with DB where clause using globalMap.get() not working

synfield — Thu, 20 Sep 2018 08:38:57 GMT

Thanks for your help. So just to clarify do I input my lookup ID field in
expression field and just name it, then use it in the query field of the
tPostgresqlinput component, "...WHERE myIDField = "myKeyName", or do I have
to specifically use globalMap.put("myKeyName") in the expression key name
field on tMap lookup panel?

Cheers.

Re: tMap lookup inner join with DB where clause using globalMap.get() not working

fdenis — Thu, 20 Sep 2018 08:45:35 GMT

you have to use xxx where field1=" + ((String)globalMap.get("yourKey")) + " and xxx

Re: tMap lookup inner join with DB where clause using globalMap.get() not working

synfield — Thu, 20 Sep 2018 09:40:57 GMT

Apologies for the confusion, but its because im already created a globalMap
var for this ID in tFlowToIterate. So your saying I need to create another
one in tMap called "myKey" without using globalMap.put("myKey")?

Cheers. Appreciate your help.

Re: tMap lookup inner join with DB where clause using globalMap.get() not working

fdenis — Thu, 20 Sep 2018 09:55:29 GMT

yes have a look to this sample:
https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/YWmAGtLfTRSB8ciEBqkncQ