Re: SQL question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 20 Oct 2005 00:55:50 +0200
Message-ID: <dj6itl$pg3$01$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
> 
> 

After a bit playing with your sql, i have a suspiction ( it seems to me, Tom Kyte's answer in mentioned thread was not quite correct as well ), the problem is with "correlated inline view". Indeed, my assumption is,   such thing is impossible - i would hardly imagine a valid access path for - i.e. in a correlated subquery correlation names can be propagated down a couple of levels , maybe until the limit of 255 nested subqueries, but the inline view can't be made correlated.

SQL> select e.*

   2 from emp e,
   3 (select * from dept d where d.deptno=e.deptno) civ    4 where e.depno=civ.deptno
   5 /
(select * from dept d where d.deptno=e.deptno) civ

                                      *

ERROR at line 3:
ORA-00904: "E"."DEPTNO": invalid identifier

Best regards

Maxim Received on Wed Oct 19 2005 - 17:55:50 CDT