CODEWITHSUNDEEP
sqlsql-servert-sqlssmsssms-2012
Sam
Sam

Reputation: 43

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

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?

Upvotes: 1

Views: 20105

Answers (6)

ScaisEdge
ScaisEdge

Reputation: 133380

you can't use a column alias in where ondition .. if you need you could use having (that work on the result values or subquery )

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

subquery and where

select operation from (

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

  ) t 
  where operation = 'X'

otherwise you should reuse the same code for case in where clause 

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  CASE WHEN Number like '20%' THEN 'X'
   WHEN Number like '15%' or  Number like '16%'  THEN 'Y' ELSE 'Z'
   END like 'X'

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Just another options

Using CTE

WITH CTE AS
(
  SELECT 
      CASE WHEN Number like '20%' THEN 'X'
     WHEN Number like '15%' or  Number like '16%'  THEN 'Y' ELSE 'Z'
     END Operation
     ,*
  FROM 
  TableA
)
SELECT *
FROM CTR
WHERE Operation = 'X';

Using CASE expression

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 CASE WHEN Number like '20%' THEN 'X'
           WHEN Number like '15%' or  Number like '16%'  THEN 'Y' 
           ELSE 'Z'
      END = 'X';

Now, let's see your last comment

I actually have multiple CASE WHEN statements but yeah using Number as a filter condition also works and simple.

Alright, you don't need a CASE expression for the Number column

SELECT 'X' Operation,
       --Another CASE here if needed
       ,*
FROM 
TableA
WHERE Number like '20%';

Upvotes: 1

forpas
forpas

Reputation: 164139

With the condition that you want to apply, your statement is equivalent to:

SELECT 'X' AS Operation, *
FROM 
TableA
where Number like '20%'

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use APPLY :

SELECT A.*, AA.*
FROM TableA AS A CROSS APPLY
     ( VALUES (CASE WHEN Number like '20%'  
                    THEN 'X'
                    WHEN (Number like '15%' OR [Item Number] like '16%')  
                    THEN 'Y' ELSE 'Z'
               END ) 
     ) AA(Operation)
WHERE AA.Operation = 'X';

This would helpful if you have a other filters as well. However only WHERE Number LIKE '20%' is just fine.

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

You must repeat the CASE expression in the WHERE clause, or nest this inside another SELECT statement.

Upvotes: 0

jarlh
jarlh

Reputation: 44786

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'

Upvotes: 6

Related Questions