I was told today that SQL Server was incorrectly installed on a server, because when a developer ran a query with an alias in the WHERE clause it produced an error. This was not a problem with the SQL Server install, but with developer’s understanding of SQL Server. The developer claimed that it worked on his test server. If anyone, has seen this work before I would be curious to know how.
If you run the following query you will receive an error.
SELECT LastName + ', ' + FirstName AS Name FROM Person WHERE Name like 'Smith%'
Msg 207, Level 16, State 1, Line 3 Invalid column name 'Name'.
Why? The reason is not because of the expression, but the order that SQL Server evaluates each clause of a query. SQL Server processes query clauses in the following order
- GROUP BY
- ORDER BY
Meaning that SQL Server first evaluates the FROM clause and then the WHERE clause. The alias is established in the SELECT clause, so WHERE clause does not know about the alias and thus throws an error.
The correct syntax should be:
SELECT LastName + ', ' + FirstName AS Name FROM Person.Person WHERE LastName like 'Smith%';