[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语句.