Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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  
like image 640
Jesse Webb Avatar asked May 02 '13 15:05

Jesse Webb


People also ask

How do I fix the ambiguous column name in SQL?

One of the simplest ways to solve an “ambiguous name column” error — without changing column name — is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.

What does ambiguous error mean in SQL?

Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it.

Why is my column name invalid in SQL?

An invalid column name error in SQL means that the column name violates the conditions of the column name. If you reference an object that does not exist in the table or the name exists, but you did not reference it correctly, you will get this error.

How do I rename a column in SQL?

ALTER TABLE table_name RENAME TO new_table_name; Columns can be also be given new name with the use of ALTER TABLE. QUERY: Change the name of column NAME to FIRST_NAME in table Student.


1 Answers

You are selecting the CreatedDate column twice.

  1. Explicitly via CreatedDate.
  2. 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.

like image 173
Daniel Hilgarth Avatar answered Oct 17 '22 05:10

Daniel Hilgarth