SQL IN() Clause from a list a value in a file

Highlighted
One Star

SQL IN() Clause from a list a value in a file

Hi !
I'm new here and use TOS almost for the first time.

According this query (based on an Oracle connetion) :
Select * from ORDER where ORDER_NO IN ()
and according this file "ORDER_NO_LIST.txt" :
123456
456789
How can i pass the list of values from the ORDER_NO_LIST.txt to the IN() clause of the SQL query ?
Thx for your help !
Highlighted
Five Stars

Re: SQL IN() Clause from a list a value in a file

Hi varior,
There are many different ways to do this and I believe it depends on the volume of data you'll be processing.
You could set up a tOracleInput for your Oracle connection and then a tFileDelimited for ORDER_NO_LIST.txt as a lookup into a tMap. Turn on inner join and you'll get the matches.
Second option is to pre-process ORDER_NO_LIST.txt using a tJavaRow/tPerlRow to create a string that looks like "123456,456789" and store it in global or context variable. Then do a OnSubJobOK connector to your tOracleInput where the query would look something like (in pseudocode):
"select * from ORDER where ORDER_NO IN (" + context.orderlistnostring + ")"
Either of these options will work.
Cheers,
c0utta
Highlighted
One Star

Re: SQL IN() Clause from a list a value in a file

Thanks c0utta for your reply.
Option 1 : if the query returns a big volume of data, can you confirm that the whole flow is processed by the tMap ?
I mean, if the query returns 10.000 lines, all the lines are "downloaded" and then passed to the tMap for applying the filter ?

Thx
Highlighted
Five Stars

Re: SQL IN() Clause from a list a value in a file

Hi varior,
..can you confirm that the whole flow is processed by the tMap

Yes, that is my understanding. I have seen that the new milestone release allows a dynamic inner join, but I haven't tried it myself (http://www.talendforge.org/forum/viewtopic.php?id=5913)
Cheers,
c0utta

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog