[20230329]記錄除錯sql語句遇到的問題.txt

[20230329]記錄除錯sql語句遇到的問題.txt

--//調式sql語句遇到的問題,做一個記錄.

1.環境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SCOTT@book> select * from dept Abc where abc.dept no = 10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> select * from dept Abc where ABC.deptno = 10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        
--//有一些工具生成的sql語句使用一大堆引號.例子:

SCOTT@book> select * from dept "Abc" where abc.deptno = 10;
select * from dept "Abc" where abc.deptno = 10
                               *
ERROR at line 1:
ORA-00904: "ABC"."DEPTNO": invalid identifier

SCOTT@book> select * from dept "Abc" where Abc.deptno = 10;
select * from dept "Abc" where Abc.deptno = 10
                               *
ERROR at line 1:
ORA-00904: "ABC"."DEPTNO": invalid identifier

--//一旦括號引起來問題來了,後面的where條件無論寫成Abc.deptno = 10 還是abc.deptno = 10都是報錯.
--//必須使用類似的雙引號.改寫如下:

SCOTT@book> select * from dept "Abc" where "Abc".deptno = 10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--//今天在這個細節上浪費大量時間,真實的語句很複雜,並且對方寫的別名竟然大小寫混用.不知道怎樣的sql語句模板生成的sql語句.