Using results from a Database select statement in multiple Jobs
I have set up Talend with an Oracle database.
I will then run a select against a table it comes back with the key "transaction_ID" and then value "global_tran_id"
I want to take what is inside the value key number and place it into a variable so that I can use it in another job.
However, it doesn't seem to want to do this.
How can create this so that I can use it in multiple select statements.
What I am referring to is that once I have the global tran number I want to use that number in several selects which are in
different jobs. How can I do that?
Thanks
Killoran
hi
i assume
"transaction_ID" => is the value returned by the DB
"global_tran_id" => name of your talend variable
you call your jobs from the main/parent job using tRunJob and click the tick-box 'transmit whole context' (do not double click on tRunJob - click once and then click on TAB component)
in this way *ALL* variable from your parent job will be sent to your child jobs.
is this what your are asking?
Actually the transaction_id is what I use to find the global_tran_id.
Basically I write a select statement the following way.
select * from (table1) where transaction_id = '12345'
From here I get the results which include a global_tran_id this number which is associated with
the transaction_id has multiple records inside multiple tables. I want to be able to use
the global number to find all the records inside all of the different tables.
I want to then use the global_tran_id to find all of the other records in different tables.
So what I want to do is place the global_tran_id inside a variable and use that number inside another
select statement. In a different job.
Example: select* from (table2) where global_tran_id = (global_tran_id result from first select statement).
Please keep in mind. I will have another job with the second select statement. I need to be able to have multiple jobs
so that I could have up to twenty different jobs with twenty different select statements all based off that global_tran_id.
I want to be able to populate all the jobs with the same global_tran_id and run the select statements. I understand that I can run multiple jobs. It is totally a matter of learning how to pull that global_tran_id into all the different jobs and place them inside a new select statements.
Hi put our output from select to TjavaRow. Pass the global_tran_id in the output to a context variable. Later you can use this context variable for other queries.
I keep getting the following error output row cannot be resolved. The way I have it set up is by running the select inside an oracle connection. From there I do a main connection to the TJavaRow. I have set up in the context variable option the key and value. Using the TJavaRow I have selected generate code however, it comes up with the following //Code generated according to input schema and output schema output_row.key = input_row.key; output_row.value = input_row.value; Any ideas?
Hi
Please do sync columns and press Generate code. For your use where the u can
add extra line context.global_tran_id = input_row.global_tran_id (this case will work only when u have only one row as output)
later u can pass the context variable another job for your process.
Hope this one helps
Still getting the error. Here is the exact message. Exception in thread "main" java.lang.Error: Unresolved compilation problems: output_row cannot be resolved output_row cannot be resolved row1.global_tran_id cannot be resolved or is not a field
Hi I have attached a screen shot for you help. there can only be input_row.(values) and output_row.(values) in tavaRow apart from routine and context values. If you click the code part of the screen then you can see the errors. Hope this one helps you
This is still not placing the information into the variable.
Also since the data that comes originally from the table schema is in a column which is a bigDecimal the
context variable will not work with a big decimal variable. It keeps stating the following
Exception in thread "main" java.lang.NumberFormatException
at java.math.BigDecimal.<init>(Unknown Source)
at java.math.BigDecimal.<init>(Unknown Source)
at lsie_test_project.global_transaction_id_0_1.Global_Transaction_ID.runJobInTOS(Global_Transaction_ID.java:1204)
at lsie_test_project.global_transaction_id_0_1.Global_Transaction_ID.main(Global_Transaction_ID.java:1124)
The context variable that I set up on the screen is a context.value and the type is just like the schema a BigDecimal. Even if I have editted the schema so that it is there the only time I get any answer is when it is a string variable and it will not place the information into the variable.
Still would like to be able to place the BigDecimal result from a select statement.
The schema reads: Transaction_id which is a string
Global_tran_id which is a BigDecimal
In the context variable I set up the variable as a BigDecimal
The tjavarow information is setup the following way.
//Code generated according to input schema and output schema
output_row.TRANSACTION_ID = input_row.TRANSACTION_ID;
output_row.GLOBAL_TRAN_ID = input_row.GLOBAL_TRAN_ID;
context.global_tran_id = input_row.GLOBAL_TRAN_ID;
And above is my what I have done...I am still having problems getting the results from one select statement into the actual variable.
Once I have this then I will need to create a new select in a different job which I want to use this global value in other table schema's
Any help would be greatly appreciated.
