Re: Weird SQL

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 18 May 2006 18:27:04 +0200
Message-ID: <446ca059$0$10173$636a55ce@news.free.fr>

"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 Received on Thu May 18 2006 - 11:27:04 CDT