Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2026 Agenda Now Available: Explore Sessions
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-11-02 05:49 AM

error 3707

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.

Labels (5)
Labels
295 Views
0 Likes
2 Replies
Anonymous
Not applicable
‎2018-11-02 07:36 AM
Author

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

295 Views
0 Likes
Anonymous
Not applicable
‎2018-11-02 08:03 AM
Author

Hey,

 

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

 

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;

 

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

295 Views
0 Likes