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

[synfield]

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.

[fdenis]

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

[synfield]

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.

[fdenis]

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

[synfield]

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.

[fdenis · Accepted solution]

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