Re: Weird SQL

"astalavista" <nobody_at_nowhere.com> a écrit dans le message de news: 446b7c45$0$7120$636a55ce_at_news.free.fr...
| Hi
|
| I have the following starnge behavior
| why the select returns one rows
| while the select in the IN is in error
|
| SQL> select * from (select 1 a from dual) a where a in (select a from
| (select 1 b from dual) b);
| A
| ----------
| 1
|
| SQL> select a from (select 1 b from dual);
| select a from (select 1 b from dual)
| *
| ERREUR Ó la ligne 1 :
| ORA-00904: "A": invalid identifier
|
| How can you explain that ?
|
| Thanks for your help ...
|

What is weird?

In your second select A is not defined.
In the first, A in the subquery is the A of the main query. To simplify the test, it's the same as:

SQL> select * from (select 1 a from dual) where a in (select a from dual);

         A

         1

1 row selected.

The A in the subquery is the A defined in the inline view. If you don't want to use dual:

SQL> create table t1 (c1 number);

Table created.

SQL> create table t2 (c2 number);

Table created.

SQL> select * from t1 where c1 in (select c1 from t2);

no rows selected

It's clearer like this c1 is the c1 of t1 and not of t2 of course. All expressions/columns of a query are visible to its first level subquery.

Regards
Michel Cadot Received on Wed May 17 2006 - 15:00:27 CDT