java.sql.SQLException: ORA-00933

Question

我有这个SQL代码。当我从SQL开发人员运行它时，它运行得很好。我需要从我的groovy脚本执行

sql.executeInsert("INSERT INTO s_status (C_DATE, sl_id, st_id, PARENT_st_id, STATUS, DETAILS, ACTIVE_IND) SELECT cd.cc_date, sl_id, st_id+1 st_id, st_id parent_st_id, 'CONFIRMED' status, 'SQL' details, 'Y' active_ind FROM s_status ss JOIN v_c_dates cd ON 1=1 WHERE (sl_id, st_id) IN (SELECT sl.sl_id, MAX(st_id) st_id FROM s_status ss JOIN stats sl ON ss.sl_id = sl.sl_id JOIN site si ON si.site_id = sl.site_id JOIN orders d ON d.ord_id = si.ord_id GROUP BY sl.sl_id ) and ss.active_ind = 'N' and ss.status = 'SENT'")

当我从groovy运行这个命令时，我在groovy控制台中看到了这个错误。帮我找出错误

java.sql.SQLException: ORA-00933: SQL command not properly ended

Answer 1

INSERT INTO s_status (C_DATE, sl_id, st_id, PARENT_st_id, STATUS, DETAILS, ACTIVE_IND) 
SELECT cd.cc_date, sl_id, st_id+1, st_id, 'CONFIRMED', 
'SQL', 'Y'
FROM s_status ss JOIN v_c_dates cd ON 1=1 
WHERE (sl_id, st_id) IN 
(SELECT sl.sl_id, MAX(st_id) --use table alias before st_id
 FROM s_status ss JOIN stats sl ON ss.sl_id = sl.sl_id
 JOIN site si ON si.site_id = sl.site_id 
 JOIN orders d ON d.ord_id = si.ord_id 
 GROUP BY sl.sl_id) 
and ss.active_ind = 'N' and ss.status = 'SENT'

您不需要在select语句中使用别名。相应地修改了您的声明。您还需要查看代码中需要使用表别名的注释。

Answer 2

我有过几次ORA-00933是由大小写敏感引起的案例。确保表名和列名引用与架构匹配。