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

From: Madhu <madhusreeram_at_gmail.com>
Date: Fri, 1 Jul 2011 08:04:16 -0700 (PDT)
Message-ID: <7c8b6e7e-e809-49bc-8e4b-f177bf3ff069_at_k23g2000pri.googlegroups.com>

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.

Eg:

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 <hurleyjo..._at_yahoo.com> 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