Import list value context variable

[chichibio]

Hi,

I need import the value to insert in oracle query. So...

I have file (csv or txt is the same). I need read value x,y,z.. After I store that value and use that

in oracle query like this: select * form table where value in ('x','y','z').

How to do that? 

thnx at all

best regards

[TRF]

Hi,
If x,y,z are issued from different records, use a tJavaRow to construct the "in" clause into à global variable. Then reuse it in your tOracleInpout to complete the query.

[chichibio]

mmm... I have the problem to do the first step.

How I can read and store the list of value inside the file?

After I can use tJavaRow?

The example of open file is like this: name = test.csv

a

b

c

d

without comma or other separator

[TRF]

Connect tJavaRow after tFileInputDelimited.
You will have each row one by one so it's easy to concatenate values an build the desired part of the query with something like:
context.yourVariable = context.yourVariable + ", '" + input_row. yourField + "'" ;
You'll have to finish the construction from outside of the tJavaRow to remove the 1rst character.

[chichibio]

Thnx!

Your suggest work. But I have 1 one problem. I change the file contains and I don't concatenate to have "'" or ",".

So, my file I have the follow text:

'a',

'b',

'c'

But after the query,in tLOGrow the output is just only 1 record and not 3 records.

I show you my print screen

In my oracle query I have: select * from table where idcase IN ("+context.variable+")

[TRF]

What if you execute the same query from outside of Talend (using SQL*Plus)?

[chichibio]

If I execute the same query in tOracle component without context variable, It works.

[TRF]

1rst, you have put 3 line in the input file but it seems (based on the capture), only 2 are read.

Can you print the context variable after the construction (add a System.out.println inthe tJavaRow)?

[chichibio]

Sorry, I wrong the example. You right.

So... input file I have 3 records and in tJavaRow I wrote this: context.elenco = row1.val_id;
System.out.println(context.elenco);

[TRF · Accepted solution]

You need to change the tJavaRow like this:

context.elenco = context.elenco + row1.val_id;

Else you will query only for the value 'c'.

Right?