using sql expression in toracleoutput

Four Stars

using sql expression in toracleoutput

Hi ,
I have a scenario where i need to insert dbsquence value into the target table based on certain condition. I have a flat file connected to tmap -> toracleoutput. For the Column Run id i have to populate the value based on the below condition. I tried to add the below in SQL Expression  but it is not working 
"decode("+row1.status+",'NEW',Run_Id.nextval,"+row1.runid+")"
is there any other way to achieve this.
Thanks 
VK
Community Manager

Re: using sql expression in toracleoutput

Use the Additional Columns section in the Advanced Settings tab. Add a row to the table. Name the column as name of the column you want to overwrite. Put the sequence.nextval call as a string in the SQL Expression. Set the Position to be "replace". Set the Reference Column to be the column you want to set the value for. When you run the Job this column will take the nextval from the sequence.
Four Stars

Re: using sql expression in toracleoutput

Thanks for your comments rhall But i am looking for a conditional based insert into the table.
"if the status is new then sequence.nextval else put in a predefined value"
Community Manager

Re: using sql expression in toracleoutput

OK. You are almost there, but have made a mistake in where and when you asses the change to the SQL. You will receive the value of row1.status in Java. Therefore you need to check the value in Java and build the SQL string using Java. You are checking the value of row1.status using DECODE, an Oracle function. Try doing it at Java time. An example is below....
"Select table.column1,
table.column2,
table.column3, " +
(row1.status.compareToIgnoreCase("NEW")==0 ? "sequence.nextval as column4 " : "table.column4 ") + "
table.column5,
table.column6
From table"

Something like above should solve your problem. 

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