Re: Ambiguous column names in subselects

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 17 Sep 2007 08:40:06 -0700
Message-ID: <1190043606.966209.199290@o80g2000hse.googlegroups.com>

On Sep 17, 6: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.)
>
> 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;
>
> 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).
>
> Thanks for any helping hints
>
> Markus

Take a look at the following test setup: CREATE TABLE T1 (
C1 NUMBER(10),
C2 NUMBER(10)); Table created.

CREATE TABLE T2 (
C1 NUMBER(10),
C2 NUMBER(10)); Table created.

INSERT INTO T1
SELECT
  ROWNUM,
  ROWNUM*2
FROM
  DUAL
CONNECT BY
  LEVEL<=3;

3 rows created.

INSERT INTO T2
SELECT
  ROWNUM,
  ROWNUM*2
FROM
  DUAL
CONNECT BY
  LEVEL<=3;

3 rows created.

SELECT

  T1.C1,
  T2.C1,
  T1.C2,
  T2.C2,

  ROWNUM COUNTER
FROM
  T1,
  T2;

C1 C1 C2 C2 ROWNUM ---------- ---------- ---------- ---------- ----------

         1          1          2          2          1
         1          2          2          4          2
         1          3          2          6          3
         2          1          4          2          4
         2          2          4          4          5
         2          3          4          6          6
         3          1          6          2          7
         3          2          6          4          8
         3          3          6          6          9

SELECT
  T1.*,
  T2.*,
  ROWNUM COUNTER
FROM
  T1,
  T2;

C1 C2 C1 C2 COUNTER ---------- ---------- ---------- ---------- ----------

         1          2          1          2          1
         1          2          2          4          2
         1          2          3          6          3
         2          4          1          2          4
         2          4          2          4          5
         2          4          3          6          6
         3          6          1          2          7
         3          6          2          4          8
         3          6          3          6          9

SELECT
  *
FROM
  (SELECT
    T1.*,
    T2.*,
    ROWNUM COUNTER
  FROM
    T1,
    T2)
WHERE
  COUNTER BETWEEN 1 AND 4; ERROR at line 2:
ORA-00918: column ambiguously defined

Both tables T1 and T2 have columns C1 and C2. SELECT
*
FROM
(SELECT

    T1.C1 T1_C1,
    T2.C1 T2_C1,
    T1.C2 T1_C2,
    T2.C2 T2_C2,

    ROWNUM COUNTER
  FROM
    T1,
    T2)
WHERE
  COUNTER BETWEEN 1 AND 4;      T1_C1 T2_C1 T1_C2 T2_C2 COUNTER ---------- ---------- ---------- ---------- ----------

         1          1          2          2          1
         1          2          2          4          2
         1          3          2          6          3
         2          1          4          2          4

Note that the ordering of the rows may change - with the Cartesian join, there is noting that says that the following results will not occur:

T1_C1 T2_C1 T1_C2 T2_C2 COUNTER ---------- ---------- ---------- ---------- ----------

         1          2          2          2          1
         2          2          4          4          2
         3          2          6          6          3
         1          1          2          2          4

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Sep 17 2007 - 10:40:06 CDT

Re: Ambiguous column names in subselects - how resolve?