CODEWITHSUNDEEP
sqlsql-serveralias
user3320324
user3320324

Reputation: 109

SQL - using SELECT Alias in WHERE

I am having a heck of a time getting this query to work. Basically I need to do a CASE statement to get the person's NickName if they have one, otherwise use their FirstName. Then in the WHERE statement, do a LIKE statement on that above CASE statement.

I've been able to get it to work if I only do the FirstName/NickName but when I add in other columns it stops working.

Here is what I've got

SELECT
    LastName
  , Company
  , Status
  , CASE
      WHEN NickName = '' THEN FirstName
      WHEN NickName IS NULL THEN FirstName
      ELSE NickName
    END AS FName
FROM database
WHERE
       Status = 'Active'
  AND  Company = '@Company'
  AND  FName + ' ' + LastName LIKE '%@search%'
  OR   LastName + ', ' + FName LIKE '%@search%'

Obviously the above doesn't work because I'm trying to use an alias in my WHERE clause. I get

Msg 207, Level 16, State 1, Line 14
Invalid column name 'FName'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'FName'.

Any help is greatly appreciated. Thanks!

Upvotes: 0

Views: 571

Answers (3)

Spock
Spock

Reputation: 4900

You can do it without the CASE statement

SELECT *
FROM (
    SELECT
        LastName
      , Company
      , Status
      , ISNULL(NULLIF(NickName, ''), FirstName) AS FName
    FROM database
    WHERE Status = 'Active'
      AND Company = '@Company') data
WHERE (FName + ' ' + LastName LIKE '%' + @search + '%')
   OR (LastName + ', ' + FName LIKE '%' + @search + '%')

Upvotes: 1

adrianm
adrianm

Reputation: 14726

Use CROSS APPLY to create an alias

SELECT LastName
     , Company
     , Status
     , FName
FROM database
     CROSS APPLY (
         SELECT CASE WHEN NickName = '' THEN FirstName
                     WHEN NickName IS NULL THEN FirstName
                     ELSE NickName
                END AS FName
     ) AS CA1
WHERE Status = 'Active'
      AND  Company = '@Company'
      AND  FName + ' ' + LastName LIKE '%@search%'
      OR   LastName + ', ' + FName LIKE '%@search%'

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

You can use a Common Table Expression or Subquery to create the FName as a kind of virtual column of a new resultset. You can that use the resultset as a kind of virtual table and filter on that in the where clause.

SELECT *
FROM (
  SELECT
    LastName
    , Company
    , Status
    , CASE
        WHEN NickName IS NULL OR NickName = '' THEN FirstName
        ELSE NickName
      END AS FName
  FROM database
  WHERE Status = 'Active'
  AND  Company = '@Company'
) FNames
WHERE FName + ' ' + LastName LIKE '%@search%'
   OR LastName + ', ' + FName LIKE '%@search%'

Upvotes: 2

Related Questions