Re: Why I can not using alias in 11G

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 08 Feb 2010 03:54:48 +0100
Message-ID: <4B6F7CF8.8010304_at_gmail.com>



On 08.02.2010 02:16, Havel Zhang wrote:
> hi all:
> I found some query will cause ORA-00918 error(column ambiguously
> defined) in 11G, but running well in 10G.
> I give an example:
> ---------------------------
> select
>
> supplier_code,dept_code,local_name,english_name
> from
> supplier a inner join
> (select supplier_code s,
> dept_code d,
> max(rowid) r
> from supplier
> group by
> supplier_code,dept_code
> ) b on a.supplier_code =
> b.s and a.dept_code = b.d and a.rowid = b.r
>
> ---------------------------
> The query above can running well in 10G, but will cause an
> error on 11G, of course, I can rewrite the query, but who can tell me
> why, 11G have new feature ? have any parameter to solve this problem?
>
> Thank you.
>
> Havel

There was an opposite bug - not generating an ORA-00918 with ansi join and ambiguously defined columns, so that Oracle picked the columns at a random and results were inconsistent. It was fixed and now you have to alias all the columns using ansi join (at least it was my impression from the note which i referenced) , so the bug you are hitting is closed as not a bug. See Note 835701.1, Bug 7343313, Bug 6760937, Bug 5368296.

Best regards

Maxim Received on Sun Feb 07 2010 - 20:54:48 CST

Original text of this message