Problem framin SQL expression issue with java in tOracleoutput

Six Stars

Problem framin SQL expression issue with java in tOracleoutput

Hi
I'm using "tOracleoutput" component to load a table, where i'm using "update or insert" option. while inserting the component should use the sequencer in the "SQL expression" option in the advanced settings and while updating it shouldn't use that. I tried to google and found the link  "https://community.talend.com/t5/Design-and-Development/using-sql-expression-in-toracleoutput/td-p/10...

but seems like there is some problem with my syntax, any help is appreciated.

 

 

if "InsUpd.ID == null" then "oraschema.sequencename.NEXTVAL" else InsUpd.ID

(InsUpd.ID column is what coming through talend link and "oraschema.sequencename.NEXTVAL" is the oracle sequence

 

"Select " +(InsUpd.ID == null ? "oraschema.sequencename.NEXTVAL" : InsUpd.ID ) + " from dual"


Thanks
MJ

Twelve Stars

Re: Problem framin SQL expression issue with java in tOracleoutput

I can not check it right now, but test this

 

"Select " +(InsUpd.ID == null ? "oraschema.sequencename.NEXTVAL from dual" : InsUpd.ID +" from dual")
-----------
Six Stars

Re: Problem framin SQL expression issue with java in tOracleoutput

Hi

Thanks for responding, its not working... i'm getting oracle error 

 

42000   :  ORA-00936: missing expression
 - Line: 1

thanks

MJ

Twelve Stars

Re: Problem framin SQL expression issue with java in tOracleoutput

Ca you try with below one.

 

"Select " + (InsUpd.ID == null ? "oraschema.sequencename.NEXTVAL from dual" : InsUpd.ID ) + " from dual"

Manohar B
Six Stars

Re: Problem framin SQL expression issue with java in tOracleoutput

Hi

I'm getting the same error for both the solution, below i have pasted the code that is getting generated for this operation, is there anything wrong...sorry i'm not a java person...

 

String update_tOracleOutput_1 = "UPDATE "
						+ tableName_tOracleOutput_1
						+ " SET "
						+ "ID"
						+ " = "
						+ "Select "
						+ (Insert_rec.ID == null ? "oraschema.ID_SEQ.NEXTVAL from dual"
								: Insert_rec.ID + " from dual")
						+ ",COL1= ?,COL2 = ?,COL3 = ?,COL4 = ?,COL5 = ?";
				java.sql.PreparedStatement pstmtUpdate_tOracleOutput_1 = conn_tOracleOutput_1
						.prepareStatement(update_tOracleOutput_1);
				String insert_tOracleOutput_1 = "INSERT INTO "
						+ tableName_tOracleOutput_1
						+ " ("
						+ "ID"
						+ ",IF_ID,IF_FAMILY_CD,COL2,COL3,COL4,COL5) VALUES ("
						+ "Select "
						+ (Insert_rec.ID == null ? "oraschema.ID_SEQ.NEXTVAL from dual"
								: Insert_rec.ID + " from dual")
						+ ",?,?,?,?,?)";

Thanks

MJ

Twelve Stars

Re: Problem framin SQL expression issue with java in tOracleoutput

as I remember in Oracle same as in other databases:

 

construction

UPDATE JOBS SET MIN_SALARY = SELECT 10000 FROM DUAL  - illegal 

 

proper will be 

 

UPDATE JOBS SET MIN_SALARY = (SELECT 10000 FROM DUAL )

 

 

check this part of Your statements:

"ID"
						+ " = "
						+ "Select "
						+ (Insert_rec.ID == null ? "oraschema.ID_SEQ.NEXTVAL from dual"
								: Insert_rec.ID + " from dual")
						+ ",

 

 

-----------