Re: Weird SQL

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:446cc92f$0$10184$636a55ce_at_news.free.fr...
>
> 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
>

Hmm, doesn't work for me: 10.2.0.2 says

ORA-00904: "C1": invalid identifier

Tried a few other forms and still got ORA-904 if C1 is referenced below the first level in an inline view. And here's an excerpt from 10.2 docs (emphasis mine):

[quote]
A subquery in the *FROM clause* of a SELECT statement is also called an *inline view*. A subquery in the *WHERE clause* of a SELECT statement is also called a *nested subquery*. [/quote]

Note that only a subquery in WHERE clause can be called nested.

[quote]
Oracle performs a "correlated subquery" when a *nested subquery* references a column from a table referred to a parent statement *any number of levels* above the subquery. [/quote]

So this sentence states that a subquery in WHERE clause can see columns of any table referred to any of its parent statements. However, this is not true for inline views and (not completely sure, but looks like) scalar subqueries.

-- 
   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)
   http://www.dynamicpsp.com