Using Case Statement Inside in Clause

Using CASE Statement inside IN Clause

CASE returns a scalar value only. You can do this instead. (I am assuming, as per your example, that when @StatusID = 99, a StatusID value of 99 is not a match.)

select *
from MyTable
where (@StatusID = 99 and StatusID in (5, 11, 13))
    or (@StatusID <> 99 and StatusID = @StatusID)

How to use CASE statement inside a WHERE with an IN clause?

You could solve this by using OR instead of CASE:

SELECT *
FROM table
WHERE (@showListedOrSold = 0 AND id IN (1, 2, 5, 6, 10, 11))
        OR (@showListedOrSold = 1 AND id IN (1, 5, 6, 10, 11))
        OR (@showListedOrSold = 2 AND id IN (2))

Case when using IN clause

I'd use the IN operator:

SELECT HIERARCHY_TYPE,
NODE_ID,
NODE_TYPE,
NODE_NAME,
NODE_LEVEL,
PREFERRED_ALIAS,
PARENT_NODE_ID,
CASE 
  WHEN ACTIVE_INDICATOR IN ('I','U','Y') THEN 'Y'
  WHEN ACTIVE_INDICATOR IN ('D','E','N') THEN 'N'
ELSE NULL
END AS ACTIVE_INDICATOR
FROM MV_HIERARCHY MV;

Using case inside where clause

Don't use a CASE statement in a WHERE clause when you really want a simple combination of boolean evaluations.

WHERE ta.estado = 'RJ'
  AND (    m_reaplicacao = 'T'
       OR (m_reaplicacao = 'S' AND ta.id_1a_cbr = 9)
       OR (m_reaplicacao = 'N' AND ta.id_1a_cbr <> 9)
      )

If for some reason you really do want to use a CASE statement, you'd need the CASE to return a value that you check in the WHERE clause. For example

WHERE ta.estado = 'RJ'
  AND (CASE WHEN m_reaplicacao = 'S' AND ta.id_1a_cbr = 9 
            THEN 1
            WHEN m_reaplicacao = 'N' AND ta.id_1a_cbr <> 9 
            THEN 1
            WHEN m_reaplicacao = 'T'
            THEN 1
            ELSE 2
        END) = 1

This is not generally the clearest way to express this sort of condition, however.

using case statement in a where clause

The branches of a case expression can only return values, not additional expressions to be evaluated in the where condition. You could, however, simulate this behavior with the and and or logical operators:

select    *
from      ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where     (ScheduleDate = testdate) and
          ((HF.IsHoliday = 1 and overtime = 1 and makeup = 0) or
           (overtime = 0 and Makeup = 0)) and
          DOW = 5 
order by  ActivityStartTime

Note that you have makeup = 0 on both branches of the case expression in the question (or both sides of the or in the answer), so you could extract it out of it and simplify the condition a bit:

select    *
from      ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where     ScheduleDate = testdate and
          makeup = 0 and
          ((HF.IsHoliday = 1 and overtime = 1) or
           overtime = 0) and
          DOW = 5 
order by  ActivityStartTime

SQL use CASE statement in WHERE IN clause

No you can't use case and in like this. But you can do

SELECT * FROM Product P    
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN  (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

BTW you can reduce that to

SELECT * FROM Product P    
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

WHERE inside CASE statement

You're trying to build a conditional WHERE clause, yes? I'd think something like this would work pretty well for what you want to do:

WHERE (EXISTS (SELECT 1 FROM Status_table WHERE ID = 3) 
        AND Item_Table.Status != 3)
        OR
        NOT EXISTS (SELECT 1 FROM Status_table WHERE ID = 3) 

---

Related Topics