Re: Why I can not using alias in 11G

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 8 Feb 2010 13:11:12 -0800 (PST)
Message-ID: <4495aa62-f91e-48a2-aa9a-95f0a939f249_at_l19g2000yqb.googlegroups.com>

On Feb 8, 2:12 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
> On 2ÔÂ8ÈÕ, ÏÂÎç2ʱ22·Ö, Serge Rielau <srie..._at_ca.ibm.com> wrote:
>
> > That's odd.. the query looks fine by me.
> > Assuming that the issue is the GROUP BY clause then it is correct NOT to
> > specify the column aliases here because they are used for the output of
> > the SELECT list. GROUP BY is processed before SELECT.
> > The GROUP BY cannot see e.g. "D".
> > From an ANSI point of view I see nothing wrong with this query.
>
> > --
> > Serge Rielau
> > SQL Architect DB2 for LUW
> > IBM Toronto Lab
> > From an ANSI point of view I see nothing wrong with this query.
>
> yes, the query work fine in 10g, but cause ora-918 in 11G.

If you prefix the select list supplier_code column with a.supplier_code does the error go away?

I am not sure if Oracle considers an inline view to be a sub-query but the Oracle SQL manual specifically states to always alias all subquery  columns:

>> ver 10gR2 SQL Ch 9 'Using Subqueries' >>
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
<<

Also if prefixing does not eliminate the issue, try using a normal pre- -92 join syntax. That should get you around the issue.

HTH -- Mark D Powell -- Received on Mon Feb 08 2010 - 15:11:12 CST