Prev: So whats up with the 11.2 java security hole?
Next: Best way to duplicate 11g table and range partition
From: Mark D Powell on 8 Feb 2010 16:11
On Feb 8, 2:12 am, Havel Zhang <havel.zh...(a)gmail.com> wrote:
> On 2ÔÂ8ÈÕ, ÏÂÎç2ʱ22·Ö, Serge Rielau <srie...(a)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 sub-
query 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-
ANSI-92 join syntax. That should get you around the issue.

HTH -- Mark D Powell --
First  |  Prev  | 
Pages: 1 2
Prev: So whats up with the 11.2 java security hole?
Next: Best way to duplicate 11g table and range partition