Ambiguous column name error
-
It might if you're being inconsistant. Such as prefixing with the table name in the WHERE clause if you're using table aliases in the SELECT statement.
-
Indeed, SQLUA-bol states
"Column aliases in ORDER BY clause cannot be prefixed by table alias "
With the example :
For example, the following query executes in SQL Server 2000, but returns an error in SQL Server 2005:
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY p.l
Thats why it generates just a warning with prefixed columns in the order by clause
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
And why does this work?
select id, name
from sysobjects so
order by abcd.id
It appears to ignore the alias if it doesn't exist.
Brian
-
Seems to be a flaw in sql2000
With sql2005 SP2 + cumul HF *
the query
select id, name
from sysobjects so
order by abcd.id
results in
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "abcd.id" could not be bound.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I use table aliases in my queries all the time. All of us at my office do. I don't recall that we had that warning in the UA when we converted from 2000 to 2005.
FYI: The "abcd.id" doesn't work in SQL 2005 at all, regardless of server pack config. I've accidently used stuff like that in RTM and SP1 and gotten the "could not be bound" error.
-
I think I figured it out. If you alias a COLUMN in your select statement, you can't prefix that ALIAS with a table alias. Not a problem for me as I don't use column aliases in my order by clauses, I use the underlying column name with the table alias.
-
AH! That makes sense.
Don't know why anyone would do that, though, considering that until SQL 2005 you couldn't order by column aliases.
Well, maybe other people could, but I kept getting error messages every time I tried it in SQL 2000.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply