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

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 !
One Star

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
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
One Star

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

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.