一则> ORA-00933: SQL command not properly ended 排查解决
SQL中正常执行的SQL语句:
update b_dispatch set ReferenceNum = (
case when ISNULL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end
) from b_dispatch b
left join k_docimage k on b.ModName = k.Name
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);
在ORALCE中这样会报错:ORA-00933: SQL command not properly ended
--> ORA-00933: SQL command not properly ended
update b_dispatch set ReferenceNum = (
case when NVL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end
) from b_dispatch b
left join k_docimage k on b.ModName = k.Name
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);
ORACLE下正确写法:
update b_dispatch set ReferenceNum = (
SELECT (case when NVL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end ) AS SNUM from b_dispatch b
left join k_docimage k on b.ModName = k.Name
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum)
)
where id= '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);
sql写法:
UPDATE A SET ID=B.ID FROM A,B WHERE A.NAME=B.NAME
在ORACLE数据库中不支持上面的这种UPDATE写法,update后面不能使用FROM这个关键字,应该像下面这样来写才对:
UPDATE A SET ID=( SELECT ID FROM B WHERE A.NAME=B.NAME )
(ORA-00933:SQL命令没有正确的结束)导致原因:
1:可能SQL语句中关键字前后缺少空格
2:Oracle 给表起别名时,直接在表名的后面空格别名就可以,不需要AS
3:SQL 语句中缺少关键字或者多了某个关键字,比如:AND
4:SQL 语句中缺少关键字或者多了某个标点符号,比如:,
本文来自博客园,作者:IT情深,转载请注明原文链接:https://www.cnblogs.com/wh445306/p/16751868.html