error 3707

Anonymous — Fri, 02 Nov 2018 09:49:30 GMT

Hi,

I am trying to execute a job and the following is the query that is generated.

INSERT INTO CM_ADW_OCRP_DEV_D.wrk_prod_clm_t(clm_id,prod_id,row_eff_dte,row_exp_dte,cde_caus_prop,cde_los_typ_pip,cde_los_typ_pip_ot,cde_stat_stts_clmt,i_popl_row,i_stts_lce,i_stts_lbfa,i_stts_ece,i_stts_ebfa) (SELECT stg_prod_clm_t.id_clm , stg_prod_clm_t.id_prod , stg_valtn_t.valtn_dte , '9999-12-31', (CASE cde_caus_prop WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_caus_prop END ), (CASE cde_los_typ_pip WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_los_typ_pip END ), (CASE cde_los_typ_pip_ot WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_los_typ_pip_ot END ), (CASE cde_stat_stts_clmt WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_stat_stts_clmt END ), (CASE i_popl_row WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_popl_row END ), (CASE i_stts_lce WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_lce END ), (CASE i_stts_lbfa WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_lbfa END ), (CASE i_stts_ece WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_ece END ), (CASE i_stts_ebfa WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_ebfa END ) FROM CM_ADW_OCRP_DEV_D.stg_prod_clm_t stg_prod_clm_t , CM_ADW_OCRP_DEV_D.stg_valtn_t stg_valtn_t)

and i am getting the following error

Exception in component tELTJDBCOutput_1 (wrk_prod_clm_t)
java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.07] [Error 3707] [SQLState 42000] Syntax error, expected something like a 'SELECT' keyword or a 'VALUES' keyword or a 'TRANSACTIONTIME' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword between ')' and '('.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:309)
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389)
at com.teradata.jdbc.jdbc_4.TDStatement.prepareRequest(TDStatement.java:576)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.<init>(TDPreparedStatement.java:130)
at com.teradata.jdbc.jdk6.JDK6_SQL_PreparedStatement.<init>(JDK6_SQL_PreparedStatement.java:30)
at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.constructPreparedStatement(JDK6_SQL_Connection.java:82)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1337)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1381)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1367)
at local_project.wrk_prod_clm_t_0_1.wrk_prod_clm_t.tELTJDBCMap_1Process(wrk_prod_clm_t.java:624)
at local_project.wrk_prod_clm_t_0_1.wrk_prod_clm_t.runJobInTOS(wrk_prod_clm_t.java:1017)
at local_project.wrk_prod_clm_t_0_1.wrk_prod_clm_t.main(wrk_prod_clm_t.java:800)

Any help is appreciated. Thanks in advance.

Re: error 3707

Anonymous — Fri, 02 Nov 2018 11:36:34 GMT

Hi,

Could you please execute the SQL separately and correct the syntax? The job error says it is a SQL syntax error.

Warm Regards,

Nikhil Thampi

Re: error 3707

Anonymous — Fri, 02 Nov 2018 12:03:12 GMT

Hey,

I have executed the query separately and following is the correct syntax.

SELECT stg_prod_clm_t.id_clm , stg_prod_clm_t.id_prod , stg_valtn_t.valtn_dte , '9999-12-31', (CASE cde_caus_prop WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_caus_prop END ),

(CASE cde_los_typ_pip WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_los_typ_pip END ),

(CASE cde_los_typ_pip_ot WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_los_typ_pip_ot END ),

(CASE cde_stat_stts_clmt WHEN ' ' THEN NULL ELSE stg_prod_clm_t.cde_stat_stts_clmt END ),

(CASE i_popl_row WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_popl_row END ),

(CASE i_stts_lce WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_lce END ),

(CASE i_stts_lbfa WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_lbfa END ),

(CASE i_stts_ece WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_ece END ),

(CASE i_stts_ebfa WHEN ' ' THEN NULL ELSE stg_prod_clm_t.i_stts_ebfa END ) FROM CM_ADW_OCRP_DEV_D.stg_prod_clm_t stg_prod_clm_t , CM_ADW_OCRP_DEV_D.stg_valtn_t stg_valtn_t;

There shouldn't be braces for select statement, but how to remove them in Talend?

topic Re: error 3707 in Talend Studio

error 3707

Re: error 3707

Re: error 3707