Re: Ambiguous column names in subselects - how resolve?

> 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! 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???

> 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.

> 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.

> 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??

Markus Received on Mon Sep 17 2007 - 09:50:18 CDT