tDBInput and Select where in - from Hashmap

[linguin]

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

like 

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

[Shicong_Hong]

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