Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Location
    India-Bangalore
    Posts
    8

    Unanswered: Dropped column returns result in subquery

    How does the dropped column (sample.empno) returns result in subquery ?

    create table sample as select * from emp;
    Table created

    select * from emp where empno in (select empno from sample);
    14 rows selected

    alter table sample drop column empno;
    Table altered.

    select empno from sample;
    ERROR at line 1:
    ORA-00904: "EMPNO": invalid identifier

    select * from emp where empno in (select empno from sample);
    14 rows selected

    Column empno does not exist in the sample table, but still the outer query returns the result, can somebody explain this.

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    That's interesting, it has to do with the column name being the same in both queries; you're accidentally getting the first table empno
    This also does the same thing:

    select * from emp where empno in (select empno from dual);

    If you qualify the sub-query table with an alias:

    select * from emp where empno in (select x.empno from sample x);

    You'll get an error like you'd expect
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

    Or by using exists:

    select e.* from emp e where exists (select 1 from sample s where s.empno=x.empno);
    ERROR at line 1:
    ORA-00904: "S"."EMPNO": invalid identifier

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •