近期在开发环境上运行一条SQL是正常的，换到测试环境上就出问题了，报ORA-00904 invalid identifier 。查询程序先处理内层子查询，它并不知道外层表的别名。故报错。ORACLE认为是个BUG, 后来就改掉了。下面来做一个实验：
C:\Documents and Settings\guogang>sqlplus
test/test@10.10.15.110
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 10 14:51:30 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create table test as select * from dba_objects where 1<>1;
表已创建。
SQL> create table test1 as select * from test where 1<>1;
表已创建。
SQL> SELECT (select *
2 from (SELECT *
3 FROM (SELECT N.OWNER
4 FROM test1 N
5 WHERE n.object_id = A.Object_Id) C))
6 FROM TEST A
7 WHERE object_id = 100;
WHERE n.object_id = A.Object_Id) C))
*
第 5 行出现错误:
ORA-00904: "A"."OBJECT_ID": 标识符无效
C:\Documents and Settings\guogang>sqlplus sqlplus
test/test@10.10.15.111
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 10 14:53:48 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test as select * from dba_objects where 1<>1;
表已创建。
SQL> create table test1 as select * from test where 1<>1;
表已创建。
SQL> SELECT (select *
2 from (SELECT *
3 FROM (SELECT N.OWNER
4 FROM test1 N
5 WHERE n.object_id = A.Object_Id) C))
6 FROM TEST A
7 WHERE object_id = 100;
未选定行
可以参考ask tom中的文章：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932