Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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