Re: SQL question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 19 Oct 2005 23:19:57 +0200
Message-ID: <dj6d9s$qb0$02$1@news.t-online.com>

Michel Cadot schrieb:

> "Maxim Demenko" <mdemenko_at_arcor.de> a écrit dans le message de news: 43567d18$0$6772$9b4e6d93_at_newsread4.arcor-online.net...

> | Michel Cadot schrieb:
> | > Maybe it's a silly question but i can't find the answer.
> | > Why can't Oracle resolve "a.dummy" in the following (meaningless) queries (8.1.7.4 & 9.2.0.6)?
> | >
> | > SQL> select ( select dummy
> | > 2 from ( select dummy
> | > 3 from dual b
> | > 4 where b.dummy = a.dummy
> | > 5 )
> | > 6 ) dummy
> | > 7 from dual a
> | > 8 /
> | > where b.dummy = a.dummy
> | > *
> | > ERROR at line 4:
> | > ORA-00904: "A"."DUMMY": invalid identifier
> | >
> | >
> | > SQL> select dummy
> | > 2 from dual a
> | > 3 where exists ( select 1
> | > 4 from ( select dummy
> | > 5 from dual b
> | > 6 where b.dummy = a.dummy
> | > 7 )
> | > 8 )
> | > 9 /
> | > where b.dummy = a.dummy
> | > *
> | > ERROR at line 6:
> | > ORA-00904: "A"."DUMMY": invalid identifier
> | >
> | > Regards
> | > Michel Cadot
> | >
> | >

> |

> | A quote from
> | http://asktom.oracle.com/pls/ask/f?p=4950:8:11867905451620403095::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1197999096334

> |

> | the correlation name B can only go one level down. the correlated
> | subquery may
> | ONLY reference its parent query.

> |
> |

> | Best regards

> |

> | Maxim

> 
> Thanks for your answer (and for reading a so long thread in T. Kyte forum)
> but my question was why is it so?
> I know there is this limit of only one level but why?
> Is this a conceptual limit or just Oracle optimizer one?
> 
> Regards
> Michel Cadot
> 
> 

Sorry, from your original post i didn't understood, you asked about implementation internals...

I can't answer your question, but , apparently, my post was not correct in general case - the restriction on only 1 level visibility scope doesn't apply for usual correlated subqueries - this seems to work:

SQL> SELECT dummy

   2 FROM dual a
   3 WHERE EXISTS

   4       (SELECT 1
   5        FROM dual b
   6        WHERE a.dummy = b.dummy
   7        AND EXISTS
   8            (SELECT 1
   9             FROM dual c
  10             WHERE c.dummy = a.dummy)
  11       )

  12 /

DUM

---
X

SQL>

Best regards

Maxim

Received on Wed Oct 19 2005 - 16:19:57 CDT