Six Stars

Import list value context variable

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

1 ACCEPTED SOLUTION

Accepted Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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?


TRF
11 REPLIES
Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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.

TRF
Six Stars

Re: Import list value context variable

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

Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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.

TRF
Six Stars

Re: Import list value context variable

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+")

 

primo.JPGsecondo.JPG

Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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

TRF
Six Stars

Re: Import list value context variable

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

Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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)?


TRF
Six Stars

Re: Import list value context variable

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);

 

1.JPG2.JPG

Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

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?


TRF
Six Stars

Re: Import list value context variable

It works well now.

 

Thnx so much TRF.

You are the boss of Talend

Smiley Very Happy

 

very good

thnx 

Thirteen Stars TRF
Thirteen Stars

Re: Import list value context variable

You're welcome!


TRF