00933 oracle sql长度,更新语句oracle - ORA-00933：SQL命令未正确结束(Update statement oracle - ORA-00933: SQL comman...

最新推荐文章于 2023-12-31 15:49:13 发布

科瓦利斯的鹿

最新推荐文章于 2023-12-31 15:49:13 发布

阅读量772

点赞数

文章标签： 00933 oracle sql长度

更新语句oracle - ORA-00933：SQL命令未正确结束(Update statement oracle - ORA-00933: SQL command not properly ended)

运行此更新命令时遇到一些麻烦。我不断收到错误消息“ORA-00933：SQL命令未正确结束”。任何人都可以协助我帮助我做到这一点吗？这就是我现在拥有的东西？

谢谢，乔治

UPDATE A

SET EARLIEST_STARTDATE = CASE WHEN (DATE_SUBMITTED < TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY'))

THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')

ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END

FROM TABLE1 A

INNER JOIN TABLE2 C on A.ID = C.ID

WHERE (EARLIEST_STARTDATE IS NULL) AND

(A.WS_ELIGIBILITY_STATUS = 1) AND

(A.CLAIM_ADD_CRITERIA_FLAG = 'N' OR A.CAF = 'Y' or A.CCA_CODE = 'SC' AND A.CLAIM_ADD_CRITERIA_FLAG = 'Y') AND

(ADD_MONTHS(C.DOB, 24) BETWEEN TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY') AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY'))

Having a bit of trouble running this update command. I keep getting the error message "ORA-00933: SQL command not properly ended". Can anyone assist in helping me get this correct? Here is what I have at the moment?

Thanks, George

UPDATE A

SET EARLIEST_STARTDATE = CASE WHEN (DATE_SUBMITTED < TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY'))

THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')

ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END

FROM TABLE1 A

INNER JOIN TABLE2 C on A.ID = C.ID

WHERE (EARLIEST_STARTDATE IS NULL) AND

(A.WS_ELIGIBILITY_STATUS = 1) AND

(A.CLAIM_ADD_CRITERIA_FLAG = 'N' OR A.CAF = 'Y' or A.CCA_CODE = 'SC' AND A.CLAIM_ADD_CRITERIA_FLAG = 'Y') AND

(ADD_MONTHS(C.DOB, 24) BETWEEN TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY') AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY'))

原文：https://stackoverflow.com/questions/33519309

更新时间：2020-02-14 15:02

最满意答案

Oracle不允许您在更新语句中加入。您需要使用相关子查询，例如：

UPDATE TABLE1 TOP_A

SET EARLIEST_STARTDATE = (

SELECT CASE WHEN DATE_SUBMITTED <

TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)), 'DD/MM/YYYY')

THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')

ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END

FROM TABLE1 SUB_A

INNER JOIN TABLE2 SUB_C on SUB_A.ID = SUB_C.ID

WHERE SUB_A.ID = TOP_A.ID

AND ADD_MONTHS(SUB_C.DOB, 24) BETWEEN

TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')

AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')

)

WHERE EARLIEST_STARTDATE IS NULL

AND WS_ELIGIBILITY_STATUS = 1

AND (CLAIM_ADD_CRITERIA_FLAG = 'N' OR CAF = 'Y' OR (CCA_CODE = 'SC' AND CLAIM_ADD_CRITERIA_FLAG = 'Y'))

这假设TABLE_1.ID是唯一的。子查询执行两个表之间的连接，并且它与子查询中的WHERE SUB_A.ID = TOP_A.ID子句正在更新的行相关联。

您的AND / OR部分有点不清楚，因此您可能需要重新考虑这些子句的括号，以获得满足业务需求的正确布尔结果。

在其他情况下TO_DATE(DATE_SUBMITTED,'DD/MM/YY')看起来也很可疑; 如果DATE_SUBMITTED已经是一个日期，那么直接引用它，不要为它调用to_date() 。在转换之前，您正在对字符串进行隐式转换; 而且由于YY模型，你可能会从过程中的日期开始失去这个世纪。

其他日期处理也可以简化。

Oracle doesn't let you join within an update statement. You need to use a correlated subquery, e.g.:

UPDATE TABLE1 TOP_A

SET EARLIEST_STARTDATE = (

SELECT CASE WHEN DATE_SUBMITTED <

TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)), 'DD/MM/YYYY')

THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')

ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END

FROM TABLE1 SUB_A

INNER JOIN TABLE2 SUB_C on SUB_A.ID = SUB_C.ID

WHERE SUB_A.ID = TOP_A.ID

AND ADD_MONTHS(SUB_C.DOB, 24) BETWEEN

TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')

AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')

)

WHERE EARLIEST_STARTDATE IS NULL

AND WS_ELIGIBILITY_STATUS = 1

AND (CLAIM_ADD_CRITERIA_FLAG = 'N' OR CAF = 'Y' OR (CCA_CODE = 'SC' AND CLAIM_ADD_CRITERIA_FLAG = 'Y'))

This assumes that TABLE_1.ID is unique. The subquery does the join between the two tables, and it's correlated with the row being updated by the WHERE SUB_A.ID = TOP_A.ID clause inside the subquery.

Your AND/OR section is a bit unclear so you may need to rethink the parentheses around those clauses to get the correct boolean result for your business needs.

The TO_DATE(DATE_SUBMITTED,'DD/MM/YY') in the case else looks suspicious too; if DATE_SUBMITTED is already a date then just refer to that directly, don't call to_date() for it. You're doing an implicit conversion to a string before converting back; and you're probably going to lose the century from the date in the process because of the YY model.

The other date handling could be simplified too.

2015-11-04

相关问答

我找到了一种避免这个问题的方法，但实际上我并不认为这是一个好方法，我倒下了LinqPad Oracle Driver有内部错误引起的问题。 Joeseph如果你能读懂这个，请提出你的建议。因此，请使用以下linq查询： void Main()

{

var q1 = (from pat in Pats

from patr in pat.PatRegisters

from prod in patr.PatRegisterOrgDets

...

您不需要在查询结尾处使用分号，也许它与此有关 You don't need the semicolon at the end of the query, maybe it has something to do with that

Oracle不允许使用表别名。只需删除as 。对于列别名， as是可选的，但强烈建议使用。 Oracle does not allow as for table aliases. Just remove the as. The as is optional for column aliases, but highly recommended.

executeQuery()在执行时会自动为语句添加一个分号。更改行sb.append("';"); 到sb.append("'"); 。此外，您需要在每行的结尾或开头添加空格，否则您的语句无效。 executeQuery() automatically adds a semicolon to a statement when executing it. Change the line sb.append("';"); to sb.append("'");. Also you'll need

...

对我来说，你似乎错过了一个; 两个陈述之间： insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000) ; insert into ps_tl_compleave_tbl values('4327142', 0, TO_DA

...

你的语法是正确的。只是在SQL语句的末尾错过了一个分号。工作示例： http ： //sqlfiddle.com/#！4 /107f82 / 2/2 (CREATE表语句最后错过了一个')' You're Syntax is correct. Just missing an Semicolon at the end of the SQL-Statement. Working Example: http://sqlfiddle.com/#!4/107f82/2/0 (the CREATE Tab

...

Oracle不允许您在更新语句中加入。您需要使用相关子查询，例如： UPDATE TABLE1 TOP_A

SET EARLIEST_STARTDATE = (

SELECT CASE WHEN DATE_SUBMITTED <

TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)), 'DD/MM/YYYY')

THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MO

...

正如Dmitry.P和a_horse_with_no_name所说。我只需要删除分号 - 也建议使用合格的列名。 conn.prepareStatement("select u.user_id,u.email from app_users u where u.user_id in (select g.user_id from app_users_groups_xref g where g.group_id = ?)")

对我来说简单的脑筋，谢谢。 As Dmitry.P and a_horse

...

问题是尾随分号。一旦删除...... SELECT 1 FROM DUAL

...保持活动功能开始按预期工作。 The problem is the trailing semicolon. Once removed... SELECT 1 FROM DUAL

...the keep-alive feature starts working as expected.

遗憾的是，没有简单的方法来实现这一目标。通常，参数化SQL需要理解的一件事是绑定参数只能用于值，例如字符串，数字或日期。您不能在其中放置SQL，例如列名或WHERE子句。一旦数据库具有SQL文本，它将尝试解析它并确定它是否有效，并且它将在不查看绑定参数值的情况下执行此操作。没有所有值，它将无法执行SQL。 SQL字符串SELECT * FROM TEMP_VIEW :1永远不会有效，因为Oracle不希望值立即跟随FROM TEMP_VIEW 。您需要将SQL构建为字符串，并同时构建绑定

...