Re: Weird SQL

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 19 May 2006 17:35:59 +0200
Message-ID: <446de5de$0$7668$626a54ce@news.free.fr>

"Vladimir M. Zakharychev" <bob--nospam--_at_dynamicpsp.com> a écrit dans le message de news: e4k8fa$2t3d$1_at_hypnos.nordnet.ru...
|
| "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.

This final part is not true as my example prove it (was in 10.2.0.1). And now, at least 100 work:

SQL> select (

  2  select dummy from (
  3  select dummy from (
  4  select dummy from (
  5  select dummy from (
  6  select dummy from (
  7  select dummy from (
  8  select dummy from (
  9  select dummy from (
 10  select dummy from (
 11  select dummy from (
 12  select dummy from (
 13  select dummy from (
 14  select dummy from (
 15  select dummy from (
 16  select dummy from (
 17  select dummy from (
 18  select dummy from (
 19  select dummy from (
 20  select dummy from (
 21  select dummy from (
 22  select dummy from (
 23  select dummy from (
 24  select dummy from (
 25  select dummy from (
 26  select dummy from (
 27  select dummy from (
 28  select dummy from (
 29  select dummy from (
 30  select dummy from (
 31  select dummy from (
 32  select dummy from (
 33  select dummy from (
 34  select dummy from (
 35  select dummy from (
 36  select dummy from (
 37  select dummy from (
 38  select dummy from (
 39  select dummy from (
 40  select dummy from (
 41  select dummy from (
 42  select dummy from (
 43  select dummy from (
 44  select dummy from (
 45  select dummy from (
 46  select dummy from (
 47  select dummy from (
 48  select dummy from (
 49  select dummy from (
 50  select dummy from (
 51  select dummy from (
 52  select dummy from (
 53  select dummy from (
 54  select dummy from (
 55  select dummy from (
 56  select dummy from (
 57  select dummy from (
 58  select dummy from (
 59  select dummy from (
 60  select dummy from (
 61  select dummy from (
 62  select dummy from (
 63  select dummy from (
 64  select dummy from (
 65  select dummy from (
 66  select dummy from (
 67  select dummy from (
 68  select dummy from (
 69  select dummy from (
 70  select dummy from (
 71  select dummy from (
 72  select dummy from (
 73  select dummy from (
 74  select dummy from (
 75  select dummy from (
 76  select dummy from (
 77  select dummy from (
 78  select dummy from (
 79  select dummy from (
 80  select dummy from (
 81  select dummy from (
 82  select dummy from (
 83  select dummy from (
 84  select dummy from (
 85  select dummy from (
 86  select dummy from (
 87  select dummy from (
 88  select dummy from (
 89  select dummy from (
 90  select dummy from (
 91  select dummy from (
 92  select dummy from (
 93  select dummy from (
 94  select dummy from (
 95  select dummy from (
 96  select dummy from (
 97  select dummy from (
 98  select dummy from (
 99  select dummy from (
100  select dummy from (

101 select dummy from dual b
102 where b.dummy = a.dummy

103  )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) dummy

104 from dual a
105 /
D
-
X

1 row selected.

Regards
Michel Cadot Received on Fri May 19 2006 - 10:35:59 CDT