How to retrieve specific data using IN and CASE with WHERE clause. I tried this:

Question

If the user select Northeast(37) then the output look like this: Output And this is my query:

SELECT st.STATE,*FROM Pulses_tbl_Transition_log l
    INNER JOIN Pulses_mst_state st ON st.id = l.state_id
    WHERE state_id IN CASE 
            WHEN state_id = 37
                THEN (
                        17
                        ,1
                        ,16
                        )
            ELSE '37'
            END

How can `state_id` be `37` and also be in that list of values? Your logic makes no sense. — Tim Biegeleisen, May 15 '18 at 13:23
Actually state_id is for northeast states. If user select it the i want these three states. — Sajid khan, May 15 '18 at 13:28
At this point, you should really show us sample data here. And also, show us what the expected output of your query is. I really can't even guess at what you want. — Tim Biegeleisen, May 15 '18 at 13:29
As suggested we need some details to understand what you are trying to do. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 15 '18 at 13:44

score 0 · Answer 1 · answered May 15 '18 at 13:31

the structure of the query is wrong for SQL, unless your using PLSQL. The when condition can only be applied to the select statement results to filter and alter specific results based on a condition. See this previous answer on the same Case in Select Statement

LukStorms · Accepted Answer · 2018-05-17T09:44:40.517

0

If the user selects, then I suppose you use a variable for that.

But CASE only returns one value.
But that's good enough, since one only needs to check on 1 value.

Example:

DECLARE @stateId INT = 37;

SELECT st.STATE, l.*, st.*
FROM Pulses_tbl_Transition_log l
JOIN Pulses_mst_state st ON (st.id = l.state_id)
WHERE
  (CASE
   WHEN @stateId NOT IN (37, 38) AND l.state_id = @stateId THEN 1
   WHEN @stateId = 37 AND l.state_id IN (1, 16, 17) THEN 2
   WHEN @stateId = 38 AND l.state_id IN (11, 28) THEN 3
   END) > 0;

Using OR is also an option.

DECLARE @stateId INT = 37;

SELECT st.STATE, l.*, st.*
FROM Pulses_tbl_Transition_log l
JOIN Pulses_mst_state st ON (st.id = l.state_id)
WHERE 
(  
   (l.state_id = @stateId AND @stateId NOT IN (37, 38)) OR
   (l.state_id IN (1, 16, 17) AND @stateId = 37) OR
   (l.state_id IN (11, 28) AND @stateId = 38) 
);

Just beware that AND's are evaluated before the OR's. Reference
So using parentesis helps to avoid logical misunderstandings. For example, guess what the result will be of this example SQL:

select 
(case when 1=1 OR 2=2 AND 3=0 then 'true' else 'false' end) as r1,
(case when 1=1 OR (2=2 AND 3=0) then 'true' else 'false' end) as r2,
(case when (1=1 OR 2=2) AND 3=0 then 'true' else 'false' end) as r3;

If there's an index on the state_id then for performance reasons it might be worth it to use a Dynamic SQL instead. And change the criteria in the dynamic SQL statement depending on the value of the variable.

edited May 17 '18 at 09:44

answered May 15 '18 at 14:26

LukStorms

28,916
5
31
45

Thank you. I have my expected output as well as i have learned something new in sql. – Sajid khan May 16 '18 at 07:31
@Sajidkhan Well, since you're learning, I included a bit more detail for inspiration. – LukStorms May 16 '18 at 12:50
Yes, I have seen it. Can u tell me, in a first query after 'CASE', 'and' is not working. I am adding 'AND (m.id = @Month ) AND (t_lg.h_id = @h_id)'. AND is not effecting the output. – Sajid khan May 16 '18 at 13:00
@Sajidkhan Not sure if I don't see the whole query. But one thing to keep in mind is that in SQL the AND's are evaluated before the OR's. So it really is good common-practice to add parentesis `()` where needed when using OR's. Just so you don't face a logical misunderstanding with what order the OR's and AND's get evaluated. – LukStorms May 16 '18 at 13:03
@Sajidkhan But if you added those AND criteria after the CASE then I don't see why those shouldn't have effect. Uhm, are you sure about `m.id = @Month`? Shouldn't that variable compare to some column that has the month? – LukStorms May 16 '18 at 13:06
Btw, if you have a date or datetime column. To get the month from that there's a [MONTH](https://www.w3schools.com/sql/func_sqlserver_month.asp) function. Or the [DATEPART](https://www.w3schools.com/sql/func_sqlserver_datepart.asp) function. – LukStorms May 16 '18 at 13:28
Where's clause starts with: `WHERE (CASE WHEN @stateId like '37' AND t_lg.state_id IN ('2','15', '16', '17','18','22','24') THEN 1 WHEN @stateId <> '37' AND t_lg.state_id like @stateId THEN 1 END) = 1 or (CASE WHEN @stateId like '38' AND t_lg.state_id IN ('11','28') THEN 1 WHEN @stateId <> '38' AND t_lg.state_id like @stateId THEN 1 END) = 1 AND (t_lg.month = '3') AND (sh.h_id = '9')` – Sajid khan May 17 '18 at 05:34
@Sajidkhan You don't need two CASE's for that. So then you don't need that OR either. A CASE returns on the first successfull `WHEN` condition met. f.e. `select (case when 1=0 then 1 when 2=2 then 2 when 5 in (3, 5) then 3 else 0 end)` will return 2. So just add the conditions for other special stateid's to the one CASE. Also, a `foo like 'bar'` should just be written as `foo='bar'`. – LukStorms May 17 '18 at 07:12

How to retrieve specific data using IN and CASE with WHERE clause. I tried this:

2 Answers2