Re: Ambiguous column names in subselects - how resolve?

On Mon, 17 Sep 2007 03:55:34 -0700, cipher <markus.doerschmidt_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.)
>
>Oracle has no "LIMIT"-condition like MySQL, so I need subselects as
>work-around (found at http://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;
>
>In this statement, Oracle reports and "ambiguously defined column",
>because "c" is in both tables.
>What can I do, to resolve this? 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.
>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. (I have the same problem for different
>queries over different tables).

Using * is a bad idea. If it helps you extend the code, it is an even worse idea. Only use * in ad-hoc queries, and EXISTS subqueries. Anywhere else is just bad coding.

The answer is is to alias each COLUMN.

B.

>
>
>Thanks for any helping hints
>
>Markus
Received on Mon Sep 17 2007 - 10:31:00 CDT