Talend community forum / [resolved] Execute various selects in sequence

TomasAlmeida · 2010-08-02 14:48:47

Tags: [best_practices, components, context, database, java, oracle]

I have a seller name and I need to read an id from the table "seller" using this name. Once I have the id, I need to read the seller_module and recover the module_id. After that, I need to read another table using the seller_id and module_id. I can do that using one prejob, but how to execute the select's in sequence? Is that possible?

If not, the only way to get data before execute the SQL is using a subquery?

Thanks!

nicolasdiogo · 2010-08-02 15:24:24

i would favour the SQL

since what you are asking for a way to fire a SQL, get a value;
then use that value to fire another SQL;
and so on

you would be better off doing it all in one SQL execute

JohnGarrettMartin · 2010-08-03 01:56:29

I agree with nicolas here, but in cases where this is not possible (mixed data sources) you can have your job set up like this:

tDBinput --> tPerlRow
|
onSubjobOk
|
tDBinput --> tPerlRow
|
onSubjobOk
|
tDBinput --> tPerlRow

use the tPerlRow (or tJavaRow if you prefer Java ) to save each value to a variable for re-use in the next tDBInput

alevy · 2010-08-08 05:21:57

To me this looks like a simple cascading join in a single tMap. The seller name is the main flow, the seller table is the first lookup joined on the seller name, the seller_module is the second lookup joined on the seller_id, the last table is the final lookup joined on the seller_id and module_id. If you don't want to read the three lookup tables in full, you can use the "Reload each row" option in tMap.

TomasAlmeida · 2010-08-09 08:26:30

I solved this problem using:

tOracleInput_1--> tJavaRow_1 --> tFlowtoIterate_1 --> tOracleInput_2--> tJavaRow_2 --> tFlowtoIterate_2 --> tOracleInput_2 --> ...

The code on the tJavaRow_1 is

context.setProperty("seller_id", Integer.toString(input_row.SELLER_ID));

The code on the tJavaRow_2 is

context.setProperty("module_id", Integer.toString(input_row.MODULE_ID));

In the SQL queries I use the context variables.

I worked for me.

Announcement

Unanswered posts

#1 2010-08-02 14:48:47

[resolved] Execute various selects in sequence

#2 2010-08-02 15:24:24

Re: [resolved] Execute various selects in sequence

#3 2010-08-03 01:56:29

Re: [resolved] Execute various selects in sequence

#4 2010-08-08 05:21:57

Re: [resolved] Execute various selects in sequence

#5 2010-08-09 08:26:30

Re: [resolved] Execute various selects in sequence

Board footer