Re: SQL question

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 19 Oct 2005 19:48:14 +0200
Message-ID: <435686dd$0$19244$626a54ce@news.free.fr>

"Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 43567d18$0$6772$9b4e6d93_at_newsread4.arcor-online.net...
| Michel Cadot schrieb:
| > Maybe it's a silly question but i can't find the answer.
| > Why can't Oracle resolve "a.dummy" in the following (meaningless) queries (8.1.7.4 & 9.2.0.6)?
| >
| > SQL> select ( select dummy
| > 2 from ( select dummy
| > 3 from dual b
| > 4 where b.dummy = a.dummy
| > 5 )
| > 6 ) dummy
| > 7 from dual a
| > 8 /
| > where b.dummy = a.dummy
| > *
| > ERROR at line 4:
| > ORA-00904: "A"."DUMMY": invalid identifier
| >
| >
| > SQL> select dummy
| > 2 from dual a
| > 3 where exists ( select 1
| > 4 from ( select dummy
| > 5 from dual b
| > 6 where b.dummy = a.dummy
| > 7 )
| > 8 )
| > 9 /
| > where b.dummy = a.dummy
| > *
| > ERROR at line 6:
| > ORA-00904: "A"."DUMMY": invalid identifier
| >
| > Regards
| > Michel Cadot
| >
| >

|

| A quote from
| http://asktom.oracle.com/pls/ask/f?p=4950:8:11867905451620403095::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1197999096334

|

| the correlation name B can only go one level down. the correlated
| subquery may
| ONLY reference its parent query.

|
|

| Best regards

|
| Maxim

Thanks for your answer (and for reading a so long thread in T. Kyte forum) but my question was why is it so?
I know there is this limit of only one level but why? Is this a conceptual limit or just Oracle optimizer one?

Regards
Michel Cadot Received on Wed Oct 19 2005 - 12:48:14 CDT