Re: Weird SQL

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 18 May 2006 20:00:36 +0200
Message-ID: <e4ico1$9sm$00$1@news.t-online.com>

Michel Cadot schrieb:

> "Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 446c3a61$0$4495$9b4e6d93_at_newsread2.arcor-online.net...

> | Michel Cadot schrieb:
> |
> | > SQL> select * from (select 1 a from dual) where a in (select a from dual);
> | >
> | > A
> | > ----------
> | > 1
> | >
> | > 1 row selected.
> | >
> |
> | > All expressions/columns of a query are visible to its first level subquery.
> | >
> | > Regards
> | > Michel Cadot
> | >
> | >
> |
> | And all further nested subqueries up to 255 nested levels.
> |
> | scott_at_ORA102> select *
> | 2 from (select 1 a from dual)
> | 3 where a in (select a
> | 4 from dual
> | 5 where exists (select a
> | 6 from dual
> | 7 where a in ( select a from dual))) --
> | may be continued
> | 8 /
> |
> | A
> | ----------
> | 1
> |
> |
> | Best regards
> |
> | Maxim

> 
> This is only true if you repeat the A at each level else only the first level is seen:
> 
> SQL> select * from (select a from dual)
>   2  where a in (select * from dual where exists (select a from dual));
> select * from (select a from dual)
>                       *
> ERROR at line 1:
> ORA-00904: "A": invalid identifier
> 
> Regards
> Michel Cadot
> 
> 

You a correct, i made wrong example to prove my point, however in your example "a" is never defined as alias, so the error. I still insist to think, top level identifier are passed through to the deepest level of nested subqueries and there is no need to reference this identifier in the middle of them:

scott_at_ORA102> select *

   2 from (select 1 a from dual)
   3 where a in (select 1

   4              from dual
   5              where exists (select 1
   6                            from dual
   7                            where exists (select 1
   8                                          from dual
   9                                          where exists ( select a
  10                                                       from dual))));

          A
----------
          1

Best regards

Maxim Received on Thu May 18 2006 - 13:00:36 CDT