SQL Use Case Statement in Where in Clause

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)

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

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))

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)

Using Case statement in Where clause in Oracle SQL

Your question is a bit ambiguous. I have assumed that country is an attribute in the table employees of data type VARCHAR.

SELECT * FROM employees
WHERE 
    (emp_id = v_emp_id AND country = 'USA') 
    OR (emp_id <= v_emp_id AND country != 'USA')

You might want to take a look at WHERE, OR and AND.

Quoting the OR page linked above:

If you use multiple logical operators in a statement, Oracle evaluates the OR operators after the NOT and AND operators. However, you can change the order of evaluation by using parentheses.

---

Related Topics