TSQL - Use a Derived Select Column in the Where Clause

Question

Is there a way in TSQL to do something like this:

select a,b,c,
case 
  when a=1 then 5
  when a=2 then 6
end as d
from some_table
where d=6

The actual case statement is really complex, so I'm trying to avoid repeating it in the where clause? Are there any tricks to do this?

(I think there's a trick in MySQL to use "having d=6").

Answer 1

select a,b,c FROM(
select a,b,c,
case 
  when a=1 then 5
  when a=2 then 6
end as d
from some_table
) AS t
where d=6

Answer 2

Make your posted query a sub-query and select from it where d = 6. As far as I know there is no way to reference a derived column in the same query.

Answer 3

This is a good place to use CTEs, e.g.:

WITH MassagedData (a, b, c, d) AS
(
    select a, b, c,
        case 
          when a=1 then 5
          when a=2 then 6
        end as d
    from some_table
)

SELECT a,b,c
FROM MassagedData
where d=6 

Answer 4

I'm going to agree with AlexKuznetsov on this one, but I would also add that if your query is (no matter how much more complex) limiting in the WHERE clause cases that exist in the CASE, then those CASEs will never be returned and shouldn't be selected in the first place.

For example, you're setting d to '6' where a is '2', then limiting to WHERE d = 6, so you could instead do:

SELECT a,b,c,
    6 AS d
FROM some_table
WHERE a = 2

This will return the same results in a more optimized and clean fashion. This is why, IMHO, there's no point in being able to reference a derived column.

Answer 5

Another option is to implement your case statement as a function. Especially good for conversion or calculation issues. What's nice about functions is that the 'business' logic is in one place and can easily be reused in other queries.

-- sample code not tested

CREATE FUNCTION dbo.fn_MyConvertA(

-- Add the parameters for the function here

@a int

)

RETURNS int -- for example

AS

BEGIN

-- Declare the return variable here
DECLARE @ResultVar as int

-- Add the T-SQL statements to compute the return value here
set @ResultVar = case when @a = 1 then 5 when @a = 2 then 6 else 10 end

-- Return the result of the function
RETURN @ResultVar

END GO

-- now you case write your query

select a,b,c, dbo.fn_MyConvertA(a) as d

from some_table

where dbo.fn_MyConvertA(a)=6