一则> ORA-00933: SQL command not properly ended 排查解决

ゞ浴缸里的玫瑰 2023-10-08 10:03 32阅读 0赞

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 语句中缺少关键字或者多了某个标点符号，比如：,