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

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 

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.


Need Your Help

Is it possible to convert a C string literal to uppercase using the preprocessor (macros)?

c macros c-preprocessor

Ignoring that there are sometimes better non-macro ways to do this (I have good reasons, sadly), I need to write a big bunch of generic code using macros. Essentially a macro library that will gene...

splitting large controller method into files

php codeigniter

my code is far from DRY and it needs refactored but for the time being i need to split it into files- i have a 300 line controller method that does a lot of api work when called and i just want to ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.