Re: Error in inline not detected

The first query is not invalid.

Think carefully about how CORRELATED
subqueries are supposed to work. If a
column name in the subquery does not
belong to the table in the subquery, Oracle moves out one layer to see if it belongs to a table in the next layer out.

You have written a slightly unusual correlated subquery. This is one reason why it is always a good idea to give every table an alias and qualify all columns with their table alias.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"bonminh lam" <hansmayer1962_at_hotmail.com> wrote in message
news:3c6b1bcf.0311130843.41facb7c_at_posting.google.com...


> 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?