Re: Ambiguous column names in subselects

From: <fitzjarrell_at_cox.net>
Date: Mon, 17 Sep 2007 06:35:08 -0700
Message-ID: <1190036108.005817.94890@50g2000hsm.googlegroups.com>

Comments embedded.
On Sep 17, 5:55 am, cipher <markus.doerschm..._at_gmail.com> wrote:
> Hi!
>
> I have two tables "A" and "B". Table A has columns named "a" and "c",
> table B has "b" and "c". Note, that "c" is in both tables! (In my real
> application, both tables are more complex and have about 20 columns.)
>

Then submit a properly written query with a fully-qualified select list.

> Oracle has no "LIMIT"-condition like MySQL, so I need subselects as
> work-around (found athttp://addictedtonow.com/archives/84/a-few-oracle-tips/).
> The SELECT would look like this one:
>
> SELECT *
> FROM (
> SELECT ta.*, tb.*, ROWNUM as counter
> FROM A ta, B tb
> ORDER BY ta.a
> )
> WHERE counter BETWEEN 1 AND 25;
>

No, it should look like this:

SELECT a, b, c, counter
FROM (

     SELECT ta.a, tb.b, ta.c, ROWNUM as counter
     FROM A ta, B tb
     WHERE tb.c = ta.c  -- proper join condition here, else cartesian
product
     ORDER BY ta.a

)
WHERE counter BETWEEN 1 AND 25;

I wouldn't be including 'counter' in my result set since you're using it as a limiting condition on the number of rows returned, and BETWEEN can be replaced with a <= operator:

SELECT a, b, c
FROM (

     SELECT ta.a, tb.b, ta.c, ROWNUM as counter
     FROM A ta, B tb
     WHERE tb.c = ta.c  -- proper join condition here, else cartesian
product
     ORDER BY ta.a

)
WHERE counter <= 25;

> In this statement, Oracle reports and "ambiguously defined column",
> because "c" is in both tables.

And it's a Cartesian product so what information does your query present? Nothing useful, in my estimation.

> What can I do, to resolve this?

Again, write a proper select list in both queries, and a proper join in the subquery.

> Is there a possibility to tell Oracle
> to add prefixes to the column names? (Before you answer, please read
> the next lines!)
> Due to the complexity of the application using the database, renaming
> of the table columns is no option.

Why would anyone tell you to rename table columns? Why wouldn't you write an explicit select list to return only the data you need? Why would you write a Cartesian join when it returns useless data?

> Listing the column names (and specify aliases where required) instead
> of "*" is also a bad idea, because it make extending database and
> application more difficult.

Wrong, it makes the database application more readable and only HELPS with extending the functionality, and it ALSO keeps you from having code that can break when a table change occurs.

> (I have the same problem for different
> queries over different tables).

Then you need to learn to write scalable, modifiable application code. Search this group for discussions of this same topic. You'll find your opinion in the minority.

>
> Thanks for any helping hints
>
> Markus

David Fitzjarrell Received on Mon Sep 17 2007 - 08:35:08 CDT

Re: Ambiguous column names in subselects - how resolve?