Re: Weird ORA-00918 when creating large view in 9i

On Dec 18, 2:52 pm, slumbert..._at_gmail.com wrote:
> I am getting a strange ORA-00918 "column ambiguously defined" error
> when creating a view on top of another, fairly large view. The base
> view, call it X, has 115 columns. The view I am trying to create is:
>
> create or replace view y as select
> (select min(x2.year) from x x2) c1,
> (select min(x2.year) from x x2) c2,
> (select min(x2.year) from x x2) c3,
> (select min(x2.year) from x x2) c4,
> (select min(x2.year) from x x2) c5,
> (select min(x2.year) from x x2) c6,
> (select min(x2.year) from x x2) c7,
> (select min(x2.year) from x x2) c8,
> (select min(x2.year) from x x2) c9,
> (select min(x2.year) from x x2) c10,
> (select min(x2.year) from x x2) c11,
> (select min(x2.year) from x x2) c12,
> (select min(x2.year) from x x2) c13,
> (select min(x2.year) from x x2) c14,
> (select min(x2.year) from x x2) c15,
> (select min(x2.year) from x x2) c16,
> (select min(x2.year) from x x2) c17,
> (select min(x2.year) from x x2) c18,
> (select min(x2.year) from x x2) c19,
> (select min(x2.year) from x x2) c20,
> (select min(x2.year) from x x2) c21,
> (select min(x2.year) from x x2) c22,
> (select min(x2.year) from x x2) c23,
> (select min(x2.year) from x x2) c24,
> (select min(x2.year) from x x2) c25
> from x;
>
> Looks dumb, I know, but I have simplified it for the sake of showing
> the problem. Now, the above works fine, but if I add one more
> identical sub-query to the end, c26, I get this:
>
> ORA-00918: column ambiguously defined
>
> This error really doesn't make any sense, since the new sub-query
> doesn't do anything that the preceding 25 sub-queries aren't already
> doing.
>
> Am I hitting some limitation of Oracle here? I am using 9.2.

Hi,

There are a fairly large number of listings for ORA-00918 bugs related to 9.2.0.x (varies by dot release) on metalink. Have you checked there?

HTH, Steve Received on Tue Dec 18 2007 - 14:11:37 CST