[resolved] Referencing data flow values in SQL Expression of "Additional Columns"

One Star

[resolved] Referencing data flow values in SQL Expression of "Additional Columns"

I've been researching this for some time and am at a loss for how to reference data flow values inside the SQL Expression field of the tOracleOutput "Additional Columns" feature.

I'm inserting data into an Oracle table, and one of the columns needs to be populated by a database function which takes two values as input parameters. The two input parameters are also columns in the data flow I cannot find anywhere how to do this. For example:

I have a data flow with two fields: field1 and field2.

I have an Oracle table with three columns: col1, col2, and col3

I need to insert the data into the Oracle table as follows:

field1 > col1
field2 > col2
dbFunction(field1,field2) > col3

Oracle does not allow you to reference col1 or col2 in the values clause of an insert (this is allowed in MySQL as I discovered today), so I have to find a way to reference the values from field1 and field2 in the SQL Expression field.

I thought I could just put something like this in the SQL Expression field: "dbFunction(" + row1.field1 + "," + row1.field2 + ")", but apparently, the SQL Expression field does not recognize the reference to fields in the data flow.

I found this post which was somewhat helpful:

http://talendforge.org/forum/viewtopic.php?id=77

The solution proposed here is to use the "replace" option along with a "?" character to refer to the replaced value. The problem I have with this is that it only allows you to use 1 parameter since ? represents the replaced field. I need to have more than one parameter.

Is this possible? Or is there a better way to accomplish the same thing?

Thanks in advance.
Community Manager

Re: [resolved] Referencing data flow values in SQL Expression of "Additional Columns"

Hello
Is this possible? Or is there a better way to accomplish the same thing?

Using tOracleSP to call dbFunction which reference col1 and col2 and return col3, then insert the three fileds into table, don't use the "Additional Columns" option.
Please see my screenshots.

Best regards

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

Re: [resolved] Referencing data flow values in SQL Expression of "Additional Columns"

Thanks, Shong. It worked perfectly.

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

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog