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

Six Stars

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

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

 

Screen Shot 2018-09-20 at 9.53.32 AM.pngScreen Shot 2018-09-20 at 10.04.56 AM.png

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.


Accepted Solutions
Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

View solution in original post


All Replies
Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

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

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.
Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Six Stars

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

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.


Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog