Re: Ambiguous column names in subselects - how resolve?

"cipher" <markus.doerschmidt_at_gmail.com> schreef in bericht news:1190040618.244814.245660_at_w3g2000hsg.googlegroups.com...
>> 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
>

As said by others: using * in a query is a bad idea. Using * to *shout* in your post is even worse..... if you expect serious answers.

Shakespeare Received on Mon Sep 17 2007 - 14:08:04 CDT