Prev: how much space to allow for log files??
Next: Solution: Invalid STDGMEDIUM structure error in DTS package
From: rdraider on 2 Oct 2007 16:12
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.


From: Erland Sommarskog on 2 Oct 2007 17:33
rdraider (rdraider(a)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(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: rdraider on 2 Oct 2007 18:04
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" <esquel(a)sommarskog.se> wrote in message
news:Xns99BDF09021E94Yazorman(a)127.0.0.1...
> rdraider (rdraider(a)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(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


From: Serge Rielau on 3 Oct 2007 18:06
steve wrote:
> On Oct 3, 6:58 am, Serge Rielau <srie...(a)ca.ibm.com> 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
 | 
Pages: 1
Prev: how much space to allow for log files??
Next: Solution: Invalid STDGMEDIUM structure error in DTS package