Re: Error in inline not detected

hansmayer1962_at_hotmail.com (bonminh lam) wrote:

>I was stunned this weird phenomena:
>
>SQL> select count(*)
> 2 from lova_instanz where inst_id in (
> 3 select inst_id
> 4 from struktur
> 5 where sdoku_id= 5418031 and doku_id= 2822511
> 6 )
> 7 ;
>
> COUNT(*)
>----------
> 0
>
>Elapsed: 00:00:09.69
>
>
>
>From this SQLPLUS output, one would assume that the query itself is
>valid. But watch the next query (i.e. the inline view in the query
>above):
>
>SQL> select inst_id
> 2 from struktur
> 3 where sdoku_id= 5418031 and doku_id= 2822511;
>select inst_id
> *
>ERROR at line 1:
>ORA-00904: invalid column name
>
>
>
>So why didn't Oracle recognize the first query as invalid? I tried
>this on 8.1.7.4 and 9.2.0.4 and the first invalid query was not
>recognized as such. The result of the query was of course hugely
>misleading, to say the mildest.
>
>
>Does someone hava a good explanation?

Not a good explanation, but the Count(*) is correctly 0 since the invalid sub-query did not return any rows - However I  cannot duplicate the behavior you show..When I try a similar query, the invalid column name error is produced.. Received on Thu Nov 13 2003 - 11:46:49 CST