Menu
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 
640
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.
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.
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.
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.
You are selecting the CreatedDate column twice.
CreatedDate.*.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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
