SQL Server Why is this SQL Query causing an "Ambiguous column name" error?

uurity8g 于 2023-05-28 发布在其他

关注(0)|答案(5)|浏览(179)

I have a SQL query which fails to execute:

select p.PersonID, CreatedDate, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by CreatedDate

The Person table has a PK of PersonID (int). The PotentiallyFraudulentPeople view is a query of the Person table joined with some other tables to decide if we trust a person or not. The PotentiallyFraudulentPeople view only has one column: PersonID .

When I try to execute this query, I get this error:
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'CreatedDate'.

I understand that this error is telling me that the CreatedDate column name is ambiguous and I need to preface it with my table's alias, 'p'.

This query works:

select p.PersonID, CreatedDate, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by p.CreatedDate

What I don't understand is why I need to use the 'p' alias in the ORDER BY statement and not in the SELECT column list. Also, I don't understand why I need to use the table alias at all since the PotentiallyFraudulentPeople view doesn't even have a CreatedDate column.

Can anyone explain this odd behavior?

I am using SQL Server 2008 and SSMS to execute the query.

UPDATE
Also, I tried removing the CreatedDate column from my SELECT column list and then the query no longer requires the 'p' alias in the ORDER BY . So this query works as well:

select p.PersonID, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by CreatedDate

sql-server

来源：https://stackoverflow.com/questions/16342271/why-is-this-sql-query-causing-an-ambiguous-column-name-error

5条答案

按热度按时间

1l5u6lss1#

You are selecting the CreatedDate column twice.

Explicitly via CreatedDate .
Implicitly via * .

It doesn't know which occurence you want to sort on - and it obviously doesn't realize that both occurences refer to the same column.

赞(0）回复(0）举报 2023-05-28

j91ykkif2#

And the reason that the name is ambiguous is related to the fact that the order by clause allows use of alias names so p.CreateDt and *.CreateDt both alias to CreateDt. Since order by allows alias names, it can't resolve

赞(0）回复(0）举报 2023-05-28

4szc88ey3#

You can add an alias to "CreatedDate" and sort it using the alias

select 
    p.PersonID, 
    CreatedDate [create_date], 
    * 
from 
    dbo.Person p join 
    dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by 
    [create_date]

赞(0）回复(0）举报 2023-05-28

qc6wkl3g4#

You have to specify which table that CreateDate came from. It means you have this column name in both tables. So either p.CreatedDate or pfp.CreatedDate.

赞(0）回复(0）举报 2023-05-28

oknrviil5#

if you want to use your original query, another option is to explicitly define the column's position:

Rather than:

select id, name, *  from employee order by name

Try:

select id, name, *  from employee order by 2

id would be position 1

name is position 2, etc..

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server Why is this SQL Query causing an "Ambiguous column name" error?

5条答案

相关问题

热门标签

最新问答