CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Nate Pet
Nate Pet

Reputation: 46222

Reference an alias elsewhere in the SELECT list

I have the following query:

select FirstName, LastName,
    case
        when LastName = 'Jones'
        then 'N/A'
    end as Other,
    case 
        when Other is not null 
        then 1 
    else 0 as Flag

The Flag value depends on Other but as Other is an alias, the Flag field does not recognize Other.

I guess, I can use a select within a select. Is there a better way for Flag to recognize the Other alias column?

Upvotes: 2

Views: 7864

Answers (4)

Dale K
Dale K

Reputation: 27202

Adding the CROSS APPLY solution for completeness (improving this question as a duplicate target).

Using CROSS APPLY allows a calculated value to be reused multiple times without needing to repeat the expression.

Its unlikely to affect performance, more a style choice.

select mt.FirstName, mt.LastName, o.Other
    case when o.Other is not null then 1 
    else 0 Flag
from MyTable mt
cross apply (
    values (
        case
            when mt.LastName = 'Jones'
            then 'N/A'
        end
    )
) o (Other);

Upvotes: -2

wBob
wBob

Reputation: 14379

You can also do this with a common table expression (CTE):

;WITH cte AS (
    SELECT 
        firstname, 
        lastname, 
        CASE WHEN lastname = 'Jones' THEN 'N/A' 
        END AS Other 
    FROM @t
) 
SELECT 
    firstname, 
    lastname, 
    CASE WHEN other IS NOT NULL THEN 1 
        ELSE 0 
    END AS Flag 
FROM cte

Upvotes: 1

Taryn
Taryn

Reputation: 247650

You will have to use a SELECT in a SELECT:

SELECT FirstName, LastName, Other, 
    CASE WHEN Other IS NOT NULL
         THEN 1 
         ELSE 0 END AS Flag
FROM (
    SELECT FirstName, LastName,
        CASE WHEN LastName = 'Jones'
             THEN 'N/A'
             END AS Other
    FROM yourTable
) x

Or you can reuse the CASE expression for the Other field when evaluating the Flag field:

SELECT FirstName, 
    LastName,
    CASE 
        WHEN LastName = 'Jones'
        THEN 'N/A'
    END AS Other,
    CASE WHEN LastName = 'Jones' -- your Other CASE code here
        THEN 1
        ELSE 0
    END AS flag 
FROM yourTable

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:

SELECT 
  FirstName, LastName, Other, 
  Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM 
(
  SELECT FirstName, LastName,
    CASE WHEN LastName = 'Jones'
     THEN 'N/A'
    END AS Other
  FROM dbo.table_name
) AS x;

Upvotes: 5

Related Questions