God of Source God of Source - 19 days ago 6
SQL Question

WHERE from CASE result

I have a statement - simplified

SELECT UserID,
CASE WHEN UserName = 'xxx' THEN 1 ELSE 0 AS Deleted
FROM User
WHERE Deleted = 0;


but this doesn't work because
Deleted
can't be used.

Is there a workaround? I can't solve it this way
WHERE UserName <> 'xxx'
, because in my real statement it's a huge sub select.

Answer

Wrap your original query up in a derived table. (Since column aliases aren't available in the same query's where clause.)

select *
from
(
    SELECT UserID,
           CASE WHEN UserName = 'xxx' THEN 1 ELSE 0 END AS Deleted
    FROM User
) dt
WHERE dt.Deleted = 0;