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

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

Anonymous — Sat, 16 Nov 2024 14:00:22 GMT

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 !

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

c0utta — Tue, 07 Apr 2009 00:19:16 GMT

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

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

Anonymous — Tue, 07 Apr 2009 20:29:08 GMT

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

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

c0utta — Wed, 08 Apr 2009 07:30:34 GMT

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 ( https://community.talend.com/t5/Design-and-Development/tMap-with-dynamic-sql-lookup/td-p/95658)
Cheers,
c0utta