Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 10 Jul 2002 12:14:31 +1000
Message-ID: <AFMW8.31668$Hj3.95854@newsfeeds.bigpond.com>

Hi Galen,

Take a look at my previous post (just above).

Are you sure the * is under the right column (I would have thought it should have appeared under the var column as it doesn't exist in either table).

With subqueries, if a column is defined in the subquery then great Oracle
references that, else if it's defined in the outer query then great, Oracle
references that, else error.

Your var column is not defined anywhere hence the error. The posters example was define *only* in the outer query, hence a correlated subquery.

Cheers

Richard
"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:uelec2nmx.fsf_at_hotpop.com...
> On Tue, 09 Jul 2002, dmorgan_at_exesolutions.com wrote:
> > Paul Brewer wrote:
> >
> >> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> >> news:3D2B4AEA.EF7D9D1_at_exesolutions.com...
> >> > And your concept of what a correlated sub-query should do is ...?
> >> >
> >> I didn't find this post particularly helpful.
> >> Still looking for suggestions.
> >>
> >> Thanks,
> >> Paul
> >
> > You weren't asking for help. You were wishing Oracle would change a
> > very standard SQL behavior that has, as far as I know, not caused any
> > problems for at least a million other developers.
> >
> > I was trying to point out that were your suggestion to be taken it
> > would require Oracle to break correlated subquerys.

>

> How is "select count(*) from emp where empno in (select empno from
> dept);" a correlated subquery? Are you saying all subqueries are
> correlated?

>

> > I am sorry if you didn't catch that. The query did exactly what you
> > asked it to do. Nothing more. Nothing less.

>

> I don't know. I'm getting an error.

>


>     Table Name: t1

>      Name    Null?    Type

>      ----------------------------------------- -------- -----------

>      ID     NUMBER

>


>     Table Name: t2

>      Name    Null?    Type

>      ----------------------------------------- -------- -----------

>      ID     NUMBER

>


>     SQL>select count(*) from t1 where id in (select var from t2);

>     select count(*) from t1 where id in (select var from t2)

>                                                 *

>     ERROR at line 1:

>     ORA-00904: invalid column name

>


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

>


>       COUNT(*)

>     ----------

>              1

>

> My sqlplus session says:

>


>     SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 9 21:37:33 2002

>


>     (c) Copyright 2000 Oracle Corporation.  All rights reserved.

>
>


>     Connected to:

>     Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

>     With the Partitioning option

>     JServer Release 8.1.7.0.0 - Production

>

> I believe its on a Linux.

>

> I then tried the same thing on my W2K box and got the same behaviour.

>


>     SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 9 21:47:51 2002

>


>     (c) Copyright 2000 Oracle Corporation.  All rights reserved.

>
>


>     Connected to:

>     Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

>     With the Partitioning option

>     JServer Release 8.1.7.0.0 - Production

>
>

> --
> Galen deForest Boyer
> Sweet dreams and flying machines in pieces on the ground.
Received on Tue Jul 09 2002 - 21:14:31 CDT