How to filter data of CASE WHEN statement using where condition?

Question

After allies CASE WHEN condition as column can we filter that column?

Example;

SELECT 
    CASE WHEN Number like '20%' THEN 'X'
   WHEN Number like '15%' or  Number like '16%'  THEN 'Y' ELSE 'Z'
   END Operation
   ,*
FROM 
TableA
where Operation like 'X'

I called CASE WHEN condition as 'Operation' and now I want to see only the Operation 'X' in the Operation column.

Is there a way to filter CASE WHEN condition with where clause in SSMS 2012?

Answer 1

Wrap your query up as a derived table, then you can put the new column in the WHERE clause:

select * 
from
(
    SELECT 
        CASE WHEN Number like '20%' THEN 'X'
       WHEN Number like '15%' or [Item Number] like '16%'  THEN 'Y' ELSE 'Z'
       END Operation
       ,*
    FROM 
    TableA
) dt
where Operation like 'X'