Re: Ambiguous column names in subselects - how resolve?

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 Received on Mon Sep 17 2007 - 09:58:10 CDT