[20151103]ora-00918 error.txt

[20151103]ora-00918 error.txt

--今天在除錯最佳化時遇到上述問題,語句很複雜透過例子來解析.

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--實際上存在問題的是一個檢視,有幾個union all,我抽取其中1段來執行.

SCOTT@test> select deptno,dname,loc,'','' from dept;
    DEPTNO DNAME          LOC           ' '
---------- -------------- ------------- - -
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      LONDON

SCOTT@test> select * from (select deptno,dname,loc,'','' from dept) where deptno=10;
select * from (select deptno,dname,loc,'','' from dept) where deptno=10
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

SCOTT@test> host oerr ora 918
00918, 00000, "column ambiguously defined"
// *Cause:
// *Action:

--看了半天最終明白其中幾個顯示欄位沒有表示欄位名.修改如下ok:

SCOTT@test> select * from (select deptno,dname,loc,'' x1 ,'' x2 from dept) where deptno=10;
    DEPTNO DNAME          LOC           X X
---------- -------------- ------------- - -
        10 ACCOUNTING     NEW YORK

--浪費1個多小時,不應該在這種地方栽跟頭.做一個記錄!