tDBInput and Select where in - from Hashmap

linguin — Sat, 17 Feb 2024 15:10:25 GMT

Hi all,

i have a hashmap with one Column CustomerID and maximum of 50-80 records. (from a XLSx)

CustomerID
123
456
122
..

Now i want to use this values from the Hashmap in a "where in" Subselect Statement in the tDBInput Component

select * from orders where order_customerID in (Select customerid from tHashmap)

Why i will do this that way ?
I except more Performance when i will use this Query.
The background is that the table orders has over 8 Millions records. A tMap is very slow because it reads all the 8 Millions Records from the Orders Table

Also i have no write rights to create a temporary table on the source system. If i had, i would a temporary table on the source system. But that is not possible.

How can i do this in Talend ? I read many Issues but nothing which helped me.

/Chris

Re: tDBInput and Select where in - from Hashmap

Shicong_Hong — Mon, 19 Feb 2024 02:20:05 GMT

Hello

You can read the customerID from XLSX and build the customerID string like this:

123,456,122
the CustomerID string will be used in the query as in values.

For example:

tFileInputExcel--main--tJavaFlex

|onsubjobok

tDBInput

on tJavaFlex component:

Start code:

String inStr="";

Main code:

if(inStr.equals("")){
inStr=row4.customerID;
}else{
inStr=inStr+","+row4.customerID;
}

End code:

globalMap.put("key", inStr);

on tDBInput component, write the query like this:

"select * from orders where order_customerID in ("+(String)globalMap.get("key")+")"

Hope it helps you!

Regards

Shicong

topic tDBInput and Select where in - from Hashmap in Talend Studio

tDBInput and Select where in - from Hashmap

Re: tDBInput and Select where in - from Hashmap