Error Ambiguous column name

Question

Post reply

Error Ambiguous column name

brownsea

SSCommitted

Points: 1514
More actions
August 18, 2008 at 2:39 pm

#196516

Hi,
I have the next error:
Use adventureworks
go
select name, *
from production.product
ORDER BY name
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'name'.
In SQL 2000 don't have this error
PD:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks for you answers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nary SSCrazy Points: 2869 More actions · Answer 1

Just rename the name cloumn with any other name as below.then it will works

Use adventureworks

go

select name as colname, *

from production.product

ORDER BY [name]

Try it now.

Thanks

brownsea SSCommitted Points: 1514 More actions · Answer 2

the solution is obvious, but mi question is why this error in SQL 2k5. but not in SQL 2k.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No, 2000 didn't complain about that. It was laxer on what it considered valid SQL syntaax, leading to the occasional oddity

What it's complaining about is that there are two columns called name in the select list, one you've explicitly stated and one that's there due to the *

You're saying order by name, but SQL's not sure which name. In this case they are the same column, but there are cases where two columns can have the same name, but be different, and then which should the order by order by?

Like this case...

SELECT name as x, cast(create_date as varchar(30)) as x, type_desc

from sys.objects

order by x

So, do you want it by name or by date?

Explicitly list your columns (always a good practice) and ensure you're not selecting the same column twice. Or, if you absolutely have to use that select clause, fully qualify the column name in the order by. That way SQL knows that you want to order by the column in the table and it doesn't have to worry about aliases in the select.

select name, * from sys.objects

order by sys.objects.name

Does that make sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

brownsea SSCommitted Points: 1514 More actions · Answer 4

thanks for you answers,

I just wanted to clarify this issue and understand why he did.

Thanks.

Brownsea