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 !
Two 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
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
Two 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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

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

Download