Michel Cadot schrieb:
> "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
>
>
Michel, i think , speaking of subquery one should separate 3 different
cases:
1) subquery in select list
2) subquery in from clause
3) subquery in where clause
The second one is very different from 1) and 3) and in my opinion should
be not referred as subquery, but as inline view. Oracle reference about
255 nested levels of subqueries does apply to first and third and don't
apply to the inline view. In opposite, all identifiers of outer queries
are not in the visibility scope of inline view.
It seems, it can't be or is very hard to implement to be visible (i
checked it by some other rdbms's, by mysql for example it was filled a
bug/enhancement request to provide such functionality and closed as due
to implementation nature).
It is however strange, that 10gR2 don't produce error on such costruct,
i'll check it, but maybe , things changes
There is no (imo) such thing as visibility only in the 1st level of
subquery - i've seen a lot of referrals to it in various places, but i
believe it is an odd oracle myth. It is not documented and i could not
build any test case to see it.
Best regards
Maxim
Received on Thu May 18 2006 - 14:51:13 CDT
