[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个多小时,不应该在这种地方栽跟头.做一个记录!
