SQL 2005 Ambiguous column name

I have a strange issue and was hoping somebody could explain. The below
query gives an ambiquous column name error on an install of SQL 2005
Standard, sp2. This same query works fine on another install of SQL 2000
Standard sp4 and SQL 2005 Workgroup. The order by clause is causing the
error. I know this is easily fixed, just wondering why the error only
occurs on a particular install of SQL 2005.

SELECT DISTINCT ProcessName, ProcessName
FROM ProcessLog
ORDER BY ProcessName


Thanks.

Re: SQL 2005 Ambiguous column name

rdraider (rdraider@sbcglobal.net) writes:
> I have a strange issue and was hoping somebody could explain. The below
> query gives an ambiquous column name error on an install of SQL 2005
> Standard, sp2. This same query works fine on another install of SQL 2000
> Standard sp4 and SQL 2005 Workgroup. The order by clause is causing the
> error. I know this is easily fixed, just wondering why the error only
> occurs on a particular install of SQL 2005.
>
> SELECT DISTINCT ProcessName, ProcessName
> FROM ProcessLog
> ORDER BY ProcessName

You get an error because this is incorrect SQL. ORDER BY is special,
because this is the only place where you can refer to columns in the SELECT
list. But there are two ProcessName, which of them do you want to order by?
That may seem like a silly question but consider:

select top 20 a = CustomerID, a = OrderID
from Orders
order by a

In SQL 2000, this sorts by CustomerID which is obviously a bug. It should
give you the same error message as SQL 2005 does. The fact that is
accepted on SQL 2005 workgroup, I would assume is due to that you ran it
in a database with the compatibility level set set to 80 (= SQL 2000).

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL 2005 Ambiguous column name

You are correct about the compatibility level on SQL 2005 set to 80.
I know this is incorrect SQL but it comes from a web app. I went ahead and
edited the ASP page but will need to have the developer fix it.

Thanks.


"Erland Sommarskog" wrote in message
news:Xns99BDF09021E94Yazorman@127.0.0.1...
> rdraider (rdraider@sbcglobal.net) writes:
>> I have a strange issue and was hoping somebody could explain. The below
>> query gives an ambiquous column name error on an install of SQL 2005
>> Standard, sp2. This same query works fine on another install of SQL 2000
>> Standard sp4 and SQL 2005 Workgroup. The order by clause is causing the
>> error. I know this is easily fixed, just wondering why the error only
>> occurs on a particular install of SQL 2005.
>>
>> SELECT DISTINCT ProcessName, ProcessName
>> FROM ProcessLog
>> ORDER BY ProcessName
>
> You get an error because this is incorrect SQL. ORDER BY is special,
> because this is the only place where you can refer to columns in the
> SELECT
> list. But there are two ProcessName, which of them do you want to order
> by?
> That may seem like a silly question but consider:
>
> select top 20 a = CustomerID, a = OrderID
> from Orders
> order by a
>
> In SQL 2000, this sorts by CustomerID which is obviously a bug. It should
> give you the same error message as SQL 2005 does. The fact that is
> accepted on SQL 2005 workgroup, I would assume is due to that you ran it
> in a database with the compatibility level set set to 80 (= SQL 2000).
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL 2005 Ambiguous column name

But why does this work?

SELECT CustomerID,CustomerID
FROM Orders

SELECT DISTINCT CustomerID,CustomerID
FROM Orders

This is what I was talking about in:

http://beyondsql.blogspot.com/2007/10/sql-two-standards-of-s ql-cte.html

best,
steve
www.beyondsql.blogspot.com

Re: SQL 2005 Ambiguous column name

steve wrote:
> But why does this work?
>
> SELECT CustomerID,CustomerID
> FROM Orders
>
> SELECT DISTINCT CustomerID,CustomerID
> FROM Orders
>
> This is what I was talking about in:
>
> http://beyondsql.blogspot.com/2007/10/sql-two-standards-of-s ql-cte.html
>
These are two completely separate topics.
Let's apply real life as a metaphor here:
There is no rule against (in fact it is quite likely that) you have two
kids with the same first name in class (Say: "William").
A problem only arises when you try to reference them (e.g. in the ORDER
BY clause or an outer query).
Then you better give them distinct names "Bill", "Billy", "Will", ...

The intent of a CTE is that it is referenced. The same isn't necessarily
true for columns in the select list (positional bind-out of a cursor)

ORDER BY is even more interesting here since it has to resolve against
two scopes: The "exposed" column names in the select list as well as the
columns accessible through the FROM clause. AFAIK the closer scope for
ORDER BY is the select list. So it's within the perogative of the DBMS
DBMS to raise an error here.
Things get more clearer when we enhance the example a bit:
SELECT customerid AS X, customerid * -1 AS X FROM orders ORDER BY X

The DISTINCT keyword (and GROUP BY while we're at it) has absolutely
NOTHING to do with ORDER BY.
There are plenty of other ways to de-dup a set than ORDERering. E.g.
hashing, or reliance on partitioning of various flavours.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Re: SQL 2005 Ambiguous column name

On Oct 3, 6:58 am, Serge Rielau wrote:
>The intent of a CTE is that it is referenced. The same isn't necessarily
>true for columns in the select list (positional bind-out of a cursor)

Hi,

So your explaining another aspect of 'referential' integrity. :-)
I reference it (CTE,Order By etc) the integrity is checked and
enforced.
And if I don't reference it it's my tough luck or my oversight and I
could wind up with a big mess?
Interestingly, it does resemble the logic of a FK reference in a
twisted way. But here chance seems to play a major part :-)
These queries go happly on their merry way:

SELECT a.orderid,a.customerid as Cust,a.shipcountry as
Cust,b.productid
FROM orders as a join [order details] as b
on a.orderid=b.orderid

SELECT a.orderid,a.orderid,a.customerid as Cust,a.shipcountry as
Cust,b.productid
FROM orders as a join [order details] as b
on a.orderid=b.orderid

best,
steve

www.beyondsql.blogspot.com

Re: SQL 2005 Ambiguous column name

steve wrote:
> On Oct 3, 6:58 am, Serge Rielau wrote:
>> The intent of a CTE is that it is referenced. The same isn't necessarily
>> true for columns in the select list (positional bind-out of a cursor)
>
> Hi,
>
> So your explaining another aspect of 'referential' integrity. :-)
> I reference it (CTE,Order By etc) the integrity is checked and
> enforced.
> And if I don't reference it it's my tough luck or my oversight and I
> could wind up with a big mess?
Not at all.
I presume you are aware that columns in the select list can actually be
unnamed:
SELECT c1 * c2, foo(c3) FROM T
You now have two unnamed columns returned from the select list.
That's all right, chances are your .NET variables that you're going to
bind them out to have different names anyway and your bind-out goes by
position anyway.
Obviously when you create a view:
CREATE VIEW v AS SELECT c1 * c2, foo(c3) FROM T
that view would be quite useless because you can reference the columns
So you give them names either in the view signature or by naming each
expression.

> Interestingly, it does resemble the logic of a FK reference in a
> twisted way. But here chance seems to play a major part :-)
> These queries go happly on their merry way:
>
> SELECT a.orderid,a.customerid as Cust,a.shipcountry as
> Cust,b.productid
> FROM orders as a join [order details] as b
> on a.orderid=b.orderid
>
> SELECT a.orderid,a.orderid,a.customerid as Cust,a.shipcountry as
> Cust,b.productid
> FROM orders as a join [order details] as b
> on a.orderid=b.orderid
And why shouldn't they?

This has nothing to do with luck. It's how the SQL Standard was
consciously designed. Now if you want to enforce that all expressions in
the select list must be named and be unambiguously feel free to write
your own standard. I sure am too lazy to label stuff I don't need :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Re: SQL 2005 Ambiguous column name

Hello Serge,

Your a good sport, have a sense of humor, smart and can write.
How did you manage to stay in IT?

Lets put the CTE to bed, I was only using it to make some
much broader points. The things works as advertised:)

I have been annoying people about Dataphor (www.alphora.com).
A product based on Date's outline of a future system (TTM.).
Unfortunately the concept of a table 'type' and 'variable'
is not something most ms sql users (who think net is for fishing)
are warming up too:) It uses an sql database as a respository.
I've used it with sql server but look forward to using it
with DB2 (yes you can use pass-thru queries as the result goes
thru the cli and is treated like any other table (variable).
I'm sure your plate is full but if you find some spare time
perhaps browse the site/help docs and if you download the beast
that would great. I blog about it (illustrated with relatively
simple examples to introduce the ideas) @
www.beyondsql.blogspot.com

The gentler idea is to use something like D4 as a complement
not a complete replacement for sql. Hence the emphasis on
application development where we're not necessarily updating
2 million rows:)

While I have no business connection to Alphora, I have a big
intellectual one with the general idea. Shesh, I'd like more company.
I'd love to see some impressions from a professional of your stature
(yeah somewhat brownoseish but said with sincerity :)

Thanks and best,
steve dassin
www.rac4sql.net
www.beyondsql.blogspot.com

Re: SQL 2005 Ambiguous column name

steve wrote:
> Hello Serge,
>
> Your a good sport, have a sense of humor, smart and can write.
> How did you manage to stay in IT?
They say the ultimate expert knows everything about nothing.
I guess I'm unemployable for anything else...

> Lets put the CTE to bed, I was only using it to make some
> much broader points. The things works as advertised:)
>
> I have been annoying people about Dataphor (www.alphora.com).
> A product based on Date's outline of a future system (TTM.).
> Unfortunately the concept of a table 'type' and 'variable'
> is not something most ms sql users (who think net is for fishing)
> are warming up too:) It uses an sql database as a respository.
> I've used it with sql server but look forward to using it
> with DB2 (yes you can use pass-thru queries as the result goes
> thru the cli and is treated like any other table (variable).
> I'm sure your plate is full but if you find some spare time
> perhaps browse the site/help docs and if you download the beast
> that would great. I blog about it (illustrated with relatively
> simple examples to introduce the ideas) @
> www.beyondsql.blogspot.com
>
> The gentler idea is to use something like D4 as a complement
> not a complete replacement for sql. Hence the emphasis on
> application development where we're not necessarily updating
> 2 million rows:)
I'll take a look. The job posting on the site is cute....
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab