Re: inconsistent results make me grumpy ( where is the 904? )

From: Madhu <>
Date: Fri, 1 Jul 2011 08:04:16 -0700 (PDT)
Message-ID: <>

This is expected result from my understanding. The "tablename" in the subquery , is taking the scope of the parent since it is unresolved. You can reproduce similar behavior using any two tables.


select * from emp where empno in (select empno from dept); --runs fine

select empno from dept; --fails with ora-00904

This is no different (from variable scoping point of view) from using the parent column in the "where" clause of the subquery (which is what we typically see).

  • Madhu On Jun 30, 3:17 pm, John Hurley <> wrote:
    > A repost here from my grumpy old dba blog:
    > inconsistent/unpredictable results make me grumpy ...
    > Here is a strange one. Why does one query get an ORA-00904 but the
    > same query when used as part of an IN apparently get ignored ( or not
    > noticed as an error )?
    > This query returns an error:
    > select table_name from dba_users;
    > Error at line 1
    > ORA-00904: "TABLE_NAME": invalid identifier
    > This query executes ( not very well but ... ):
    > select * from dba_tables where table_name in
    > ( select table_name from dba_users );
Received on Fri Jul 01 2011 - 10:04:16 CDT

Original text of this message