Can you use an alias in the WHERE clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you use an alias in the WHERE clause?

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-04-07 : 16:44:32

Here is the code I have:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS
concatName
FROM Person.Person
WHERE NOT concatName IS NULL
ORDER BY LastName

Here is the error message I am receiving:

Msg 207, Level 16, State 1, Line 17
Invalid column name 'concatName'.

I am using SQL Express 2012 on my PC. From this, it appears as though an alias cannot be used in the WHERE clause. Is this true, or is there a way to work around this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-04-07 : 16:55:54

The only way to refer to the alias is to rewrite the statement as a subquery or a common table expression. It's probably easier to write it like this:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS
concatName
FROM Person.Person
WHERE FirstName + MiddleName + LastName IS NOT NULL
ORDER BY LastName

Since the empty strings '' are constants you don't need to include them in the WHERE condition to check for NULLs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources