[resolved] Can the SQL Expression in "Additional Columns" be dynamically modified

Highlighted
One Star

[resolved] Can the SQL Expression in "Additional Columns" be dynamically modified

Is it possible to dynamically modify the value of the SQL Expression field in the Additional Columns section of a tOracleOutput component?
I've been trying to use a context variable to see if I can change this value on the fly, but it does not seem to be working.
Here is what I've done so far:
1. Create a context variable called var1 and assign it a value of ''ValueSetInContext''.
2. Create a flow which inserts data into an Oracle table via tOracleOutput component (using Drop table in exists and create option).
3. In the Additional Columns section, I add a new column called "DUMMY" and in the SQL Expression field I put this: context.var1
4. Run the program, and I can see that the DUMMY field got populated with this string: ValueSetInContext
5. Next, I decided to send the data flow through a tJavaRow component so I could modify the value of the context.var1 variable. In this component, I have this code:
context.var1="ValueSetIntJavaComponent";
System.out.println(context.var1);
6. Then I map the data flow from the tJavaRow component into the tOracleOutput component just as before (leaving the value of the SQL Expression as context.var1).
7. Run the program, and the DUMMY field still gets populated with ValueSetInContext instead of ValueSetIntJavaComponent.
My conclusion is that the SQL Expression gets evaluated right at the beginning of the job execution and never changes after that.
Can anyone verify this or provide an example of how to modify that value on the fly?
Thanks.

Accepted Solutions
Highlighted
Community Manager

Re: [resolved] Can the SQL Expression in "Additional Columns" be dynamically modified

Hello
You need use tFlowToIterate to iterate each row. See my screenshots.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

View solution in original post


All Replies
Highlighted
Community Manager

Re: [resolved] Can the SQL Expression in "Additional Columns" be dynamically modified

Hello
You need use tFlowToIterate to iterate each row. See my screenshots.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

View solution in original post

Highlighted
One Star

Re: [resolved] Can the SQL Expression in "Additional Columns" be dynamically modified

Thanks again. This worked as well.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog