Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Weird SQL

Re: Weird SQL

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

"Maxim Demenko" <mdemenko_at_gmail.com> a écrit dans le message de news: e4ico1$9sm$00$1_at_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

Sorry it was the worst example i ever posted. What i meant was you can:

SQL> select (select c2 from t2 where c2=c1) from t1;

no rows selected

But you can't:

SQL> select (select c2 from (select c2 from t2 where c2=c1)) from t1; select (select c2 from (select c2 from t2 where c2=c1)) from t1

                                                   *
ERROR at line 1:
ORA-00904: "C1": invalid identifier

Well, when i say you can't it's no more true. I am just checking in 10gR2 and now it works (it does not in 9iR2 and before):

SQL> select (select c2 from (select c2 from t2 where c2=c1)) from t1;

no rows selected

SQL> select (select c2 from (select c2 from (select c2 from t2 where c2=c1) where c2=c1)) from t1;

no rows selected

I don't check how many levels we can have now.

Thanks and regards
Michel Cadot Received on Thu May 18 2006 - 14:21:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US