Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Weird SQL
"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
| >
| >
|
|
|
| A
| ----------
| 1
|
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:
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