CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlcase
Greg
Greg

Reputation: 47124

TSQL - Use a Derived Select Column in the Where Clause

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").

Upvotes: 13

Views: 18466

Answers (6)

A-K
A-K

Reputation: 17090

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

Upvotes: 25

JNK
JNK

Reputation: 65167

An alternative method to this is to use CROSS APPLY:

select a,b,c,
from some_table
CROSS APPLY (SELECT case 
                    when a=1 then 5
                    when a=2 then 6
                    end) CxA(d)
where d=6

Upvotes: 0

MrTexas
MrTexas

Reputation: 340

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

Upvotes: 3

Nathan Wheeler
Nathan Wheeler

Reputation: 5932

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.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171431

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

Upvotes: 8

Nate Heinrich
Nate Heinrich

Reputation: 1805

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.

Upvotes: 2

Related Questions