[20151103]ora-00918 error.txt-优快云博客

本文记录了在调试优化过程中遇到的ORA-00918错误及其解决方案。错误出现在尝试从包含多个UNION ALL操作的复杂视图中选择数据时。通过具体的SQL语句示例展示了如何正确指定列名以避免此错误。

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1821638/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1821638/