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

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

Jl-nrd — Fri, 01 Mar 2019 10:46:42 GMT

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

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

Anonymous — Fri, 01 Mar 2019 12:02:19 GMT

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.

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

akumar2301 — Fri, 01 Mar 2019 14:37:36 GMT

you could use tjavaflex

suppose your input is

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.

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

Jl-nrd — Fri, 01 Mar 2019 14:46:16 GMT

Thanks to you 2 you show me the way !

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"))