CODEWITHSUNDEEP
sql-server
Jacob
Jacob

Reputation: 3698

Use The Selected value of the CASE WHEN inside the WHERE Clause

I have this SQL query:

SELECT date,
    CASE WHEN h_score > v_score
    THEN
        ho_t_id
    ELSE
        v_t_id
    END
FROM Games

I would like to use the selected value (of the CASE WHEN statement) inside the WHERE clause. I try to do something like that:

SELECT date,
    CASE WHEN h_score > v_score
    THEN
        ho_t_id
    ELSE
        v_t_id
    END AS T
FROM Games
WHERE T = '1234'

This (of course) doesn't work.

How can I do that?

Thank you !

Upvotes: 0

Views: 51

Answers (3)

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16259

SELECT date, T FROM (
    SELECT date,
        CASE WHEN h_score > v_score
        THEN
            ho_t_id
        ELSE
            v_t_id
        END AS T
    FROM Games
) g
WHERE g.T = '1234'

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Simply repeat CASE in WHERE clause:

SELECT date,
    CASE WHEN h_score > v_score
    THEN
        ho_t_id
    ELSE
        v_t_id
    END AS T
FROM Games
WHERE (CASE WHEN h_score > v_score
    THEN
        ho_t_id
    ELSE
        v_t_id
    END) = '1234'

Upvotes: 0

radar
radar

Reputation: 13425

you can have your query inside a CTE and have the where clause out side.

with cte 
as
(
    SELECT date,
           CASE 
           WHEN h_score > v_score
           THEN ho_t_id
           ELSE v_t_id
           END AS T
    FROM Games
)
select * from cte where T = '1234

Upvotes: 1

Related Questions