Re: mysterious behaviour where = (SELECT JAHR ...
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 25 Sep 2010 07:00:55 -0700 (PDT)
Message-ID: <0e92da83-f396-494c-b68a-b3e990c4c331_at_28g2000yqm.googlegroups.com>
On Sep 24, 7:17 am, Kay Kanekowski <kay.kanekow..._at_web.de> wrote:
> Hallo Norbert,
> try it with aliases and you see that your first statement fails too.
>
> select * from TST_TAB t WHERE YEAR = (SELECT y.YEAR FROM TST_ONE_YEAR
> y);
>
> As Michael said, the year column is coming from tst_tab and this is
> true for all rows.
>
> works as designed ... but not as expected ;-)
>
> tschüss
> Kay
Date: Sat, 25 Sep 2010 07:00:55 -0700 (PDT)
Message-ID: <0e92da83-f396-494c-b68a-b3e990c4c331_at_28g2000yqm.googlegroups.com>
On Sep 24, 7:17 am, Kay Kanekowski <kay.kanekow..._at_web.de> wrote:
> Hallo Norbert,
> try it with aliases and you see that your first statement fails too.
>
> select * from TST_TAB t WHERE YEAR = (SELECT y.YEAR FROM TST_ONE_YEAR
> y);
>
> As Michael said, the year column is coming from tst_tab and this is
> true for all rows.
>
> works as designed ... but not as expected ;-)
>
> tschüss
> Kay
Actually this should not be unexpected behavior since the SQL manual explicitly states this requirement: "If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view."
Reference: Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 section Using Subqueries
HTH -- Mark D Powell -- Received on Sat Sep 25 2010 - 09:00:55 CDT