Hello,
Wonder why NOT IN clause is not throwing ORA-00904: Invalid Identifier even if the column is not present in a Table. Below is the illustration
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table A (col1 number);
Table created.
SQL> create table B (col2 number);
Table created.
SQL> select col1 from A where col1 not in (select col1 from b);
no rows selected
SQL> select col1 from A where col1 not in (select col3 from b);
select col1 from A where col1 not in (select col3 from b)
*
ERROR at line 1:
ORA-00904: "COL3": invalid identifier
SQL> spool off;
----------------------------------------------------------------------------------------------------------
You can see from the above even table B doesn't have col1, Oracle is not throwing an error and it is happening only if you use the same column name in where clause and subquery. Please let me know is it feature or Bug?
Thanks,
Umakanth