Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    19

    Thumbs down Unanswered: Inner 3rd level sub-query does not recognize outer query table

    Hello,

    We’ve came a cross a case that we need to perform a query that is nested 3 levels, as far as I know there should not be any problem with supplying an answer to such a query, but some Oracle servers on our site do not deliver to requested functionality, the following is a small test case that shows the problem.

    SQL> drop table t1;

    Table dropped.

    SQL> create table t1(c1 number);

    Table created.

    SQL> drop table t2;

    Table dropped.

    SQL> create table t2(c1 number);

    Table created.

    SQL>
    SQL> insert into t1 (c1) values (1);

    1 row created.

    SQL> insert into t1 (c1) values (2);

    1 row created.

    SQL> insert into t1 (c1) values (3);

    1 row created.

    SQL> insert into t2 (c1) values (1);

    1 row created.

    SQL> insert into t2 (c1) values (2);

    1 row created.

    SQL> insert into t2 (c1) values (3);

    1 row created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> select * from t1 where exists (select 1 from t2 where t2.c1 = t1.c1);

    C1
    ----------
    1
    2
    3

    When nesting the inner query one more level (into 3rd level), it does not work:

    SQL> select * from t1 where exists (select * from (select 1 from t2 where t2.c1 = t1.c1));
    select * from t1 where exists (select * from (select 1 from t2 where t2.c1 = t1.c1))
    *
    ERROR at line 1:
    ORA-00904: "T1"."C1": invalid identifier

    Using explicit aliasing for t1 also do not work:

    SQL>
    SQL> select * from t1 x where exists (select * from (select 1 from t2 where t2.c1 = x.c1));
    select * from t1 x where exists (select * from (select 1 from t2 where t2.c1 = x.c1))
    *
    ERROR at line 1:
    ORA-00904: "X"."C1": invalid identifier

    This behavior is not consistent, we have several servers on our site that the last two queries do return the 1,2,3 rows.

    Looks like a possible bug.

    Comments?

    TIA,

    Tal Olier (tal.olier@gmail.com)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not sure it's a bug, I think it's just the way it works. The docs say:
    Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
    Note that it only says parent, not any ancestor!

  3. #3
    Join Date
    May 2004
    Posts
    19
    Quote Originally Posted by andrewst
    I'm not sure it's a bug, I think it's just the way it works. The docs say:

    Note that it only says parent, not any ancestor!
    Checkout:
    http://forums.oracle.com/forums/thre...hreadID=378604

    Thanks,

    Tal.

Posting Permissions

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