How to pass an array or list as a globalMap variable for a WHERE x IN clause of a DBinput

Three Stars

How to pass an array or list as a globalMap variable for a WHERE x IN clause of a DBinput

I'd like to generate a WHERE x IN (a,b,c,...) clause to use in a DBinput querying a very big table.
the "IN" list is coming from a previous Tmap in my job.

 

For now I use a TflowToIterate followed by a Tmap with a reload at each row lookup but it works with a where x=GlobalMap.get("myvar"), thus generating many iteration/ row flow.

 

Question is how can I generate an array/list from a tmap and use it latter on my job in the IN list of a where clause. i guess globalMap variable is the answer, but i'm struggling on the "how".

 

Thanks in advance Smiley Happy

Community Manager

Re: How to pass an array or list as a globalMap variable for a WHERE x IN clause of a DBinput

I'm not sure whether you want an Array or whether you simply want to append to a String variable which can be used in your IN in your query. I suspect the String will work better for you given what you have described. Does this sound right? Also, I think it would be better to build the list in a tJavaFlex rather than a tMap. Then you can easily manipulate the list in Java without having to worry about doing it inline.

Eleven Stars

Re: How to pass an array or list as a globalMap variable for a WHERE x IN clause of a DBinput

you could use tjavaflex 

 

suppose your input is 

1

2

3

 

tJavaFlex

 

Begin Code

final char COMMA = ',';
// String builders for Selection Number List .
StringBuilder selectionListBuilder = new StringBuilder();

 

Main Code

// Append comma to the number list.
if (selectionListBuilder length() != 0) {
selectionListBuilder.append(COMMA);
}
// Append number to selection list.
selectionListBuilder.append(row1.listelement);

 

End Code 

// Get the number list.
String selectionList = selectionListBuilder.toString();


// Set the global variables.
if (selectionList length() != 0) {
globalMap.put("NumberList", selectionList );
//context.NumberList=selectionList.substring(0,selectionList.length());
}

 

Note that Some database has limit on Number of element in IN clause e.g. Oracle has limit of 1000. So please build your Query expression accordingly.

Regards
Abhishek KUMAR
Three Stars

Re: How to pass an array or list as a globalMap variable for a WHERE x IN clause of a DBinput

Thanks to you 2 you show me the way Smiley Happy !

 

I've used a tjavarow at the output of my first tMap

 

code :

if ((String)globalMap.get("inList") == "")
{
globalMap.put("inList", "'"+input_row.elem+"'");
}else{
globalMap.put("inList", (String)globalMap.get("inList")+",'"+ input_row.elem+"'");
}

After that in the next subjob, on my lookup DBinput of my second tMap i could use WHERE x IN (globalMap.get("inList"))

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

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog