Re: Ambiguous column names in subselects - how resolve?

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

On Sep 17, 9:58 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
> On Sep 17, 9:50 am, cipher <markus.doerschm..._at_gmail.com> wrote:
>
> > > 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;
>
> > Point 1: WHERE is *not* required for this example!
>
> So a Cartesian product is what you want. Fine.
>
> > Point 2: SELECT a,b,c,counter *does* not change anything here because
> > Oracle still reports
> > the ambiguity error. Which column "c" should be used here???
>
> READ the subquery and you'll see it DOES change things here as only
> ONE column c, from table a, is returned in the subselect.
>
>
>
> > > And it's a Cartesian product so what information does your query
> > > present? Nothing useful, in my estimation.
>
> > This is only an *example*, to show you the problem.
>
> Which doesn't show much of anything, really.
>
> > > 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?
>
> > The join in the subselect is *not* the problem. The problem is, that
> > the
> > result set contains two columns with identical names.
>
> And THAT problem has been addressed. You simply missed the solution.
>
> > > 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.
>
> > How would you write those code, if it has to work on *complete
> > different* tables??
>
> Now you change the conditions of execution. And I wouldn't be
> changing anything in the general method of access. Yes, it requires
> that you KNOW your tables and code for them accordingly (read up in
> this newsgroup and you'll find that 'select *' is never a solution
> for anything in a properly coded application), but ANY good
> application code uses a proper select list to return values, NOT
> 'select *'.
>
> > Markus
>
> You've asked for advice, and it's been given. Now you decide that you
> know more than those providing the advice. You need to read the
> responses far more carefully than you do; your 'concerns' were
> addressed in my original response.
>
> David Fitzjarrell

And the query I supplied does NOT throw the 'ambiguous column error':

SQL> create table a(a number, c varchar2(10));

Table created.

SQL>
SQL> create table b(b number, c varchar2(10));

Table created.

SQL>
SQL> insert all
  2 into a
  3 values (1, 'Test1')
  4 into a
  5 values (2, 'Test2')
  6 into a
  7 values (3, 'Test3')
  8 into a
  9 values (4, 'Test4')
 10 into a
 11 values (5, 'Test5')
 12 select * from dual;

5 rows created.

SQL>
SQL>
SQL> insert all

  2 into b
  3 values (1, 'Test1')
  4 into b
  5 values (2, 'Test2')
  6 into b
  7 values (3, 'Test3')
  8 into b
  9 values (4, 'Test4')
 10 into b
 11 values (5, 'Test5')
 12 select * from dual;

5 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT a, b, c, counter
  2 FROM (

  3               SELECT ta.a, tb.b, ta.c, ROWNUM as counter
  4               FROM A ta, B tb
  5               WHERE tb.c = ta.c  -- proper join condition here,
else cartesian product
  6               ORDER BY ta.a
  7           )
  8           WHERE counter BETWEEN 1 AND 25;

         A          B C             COUNTER
---------- ---------- ---------- ----------
         1          1 Test1               1
         2          2 Test2               2
         3          3 Test3               3
         4          4 Test4               4
         5          5 Test5               5

SQL> David Fitzjarrell Received on Mon Sep 17 2007 - 10:06:50 CDT