java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

Five Stars

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

Hello, 

 

I am trying to set up a basic Oracle ELT job with a MERGE "Action on data" for update/insert. The specific error when I run my ELT job is "Exception in component tELTOracleOutput_1 (D_Lead_Inc_ELT) java.sql.SQLSyntaxErrorException: ORA-00936: missing expression"

Attached is my job. Any pointers here are greatly appreciated.

 

Doug

Thirteen Stars

Re: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

in Your original Job design - You are not define keys for Output table (on picture - modified, right LEAD_ID was missed):

Screen Shot 2018-06-01 at 10.21.09.png

 

in this case Talend generate construction like:

				String mergeQuery = "MERGE INTO "
						+ tableName_tELTOracleOutput_1 + " target" + " USING ("
						+ select_query_tELTOracleOutput_1 + ") source ON (" +

						"" + ")";

				mergeQuery += " WHEN MATCHED THEN UPDATE SET target.LEAD_TYPE=source.LEAD_TYPE, SKIPPED for make it shorter ";

				mergeQuery += "WHERE " + "source.lead_id = target.lead_id";

this construction - illegal

 

when You define Key for source and target, it would be:

				String mergeQuery = "MERGE INTO "
						+ tableName_tELTOracleOutput_1 + " target" + " USING ("
						+ select_query_tELTOracleOutput_1 + ") source ON (" +

						"target.LEAD_ID=source.LEAD_ID" + ")";

				mergeQuery += " WHEN MATCHED THEN UPDATE SET target.LEAD_TYPE=source.LEAD_TYPE,SKIPPED ";

				mergeQuery += "WHERE " + "source.lead_id = target.lead_id";
-----------