topic Re: using sql expression in toracleoutput in Talend Studio

using sql expression in toracleoutput

Anonymous — Wed, 24 Jun 2015 17:23:49 GMT

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

Re: using sql expression in toracleoutput

Anonymous — Wed, 24 Jun 2015 17:32:50 GMT

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.

Re: using sql expression in toracleoutput

Anonymous — Thu, 25 Jun 2015 06:55:33 GMT

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"

Re: using sql expression in toracleoutput

Anonymous — Thu, 25 Jun 2015 09:01:10 GMT

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.