using case statement in a where clause

Question

Hello I am missing something because my code errors.

select * from ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate

where (ScheduleDate = testdate)
and
(Case 
 when HF.IsHoliday = 1 then (overtime = 1 and makeup = 0)
 else
(overtime = 0 and Makeup = 0)
end
)
and
DOW = 5 
order by ActivityStartTime

I've attempted several combinations and each one errors at either the first equal sign or the second. What am I missing?

user_2422776 · Answer

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

user_6026385 · Answer

If you are still wanting to know how to utilize a CASE ~~Statement~~ Expression in a WHERE Clause the CASE Expression must be compared to a value as that is the syntax understood for conditions contained within a WHERE Clause. See below a mock example.

SELECT *
FROM ##ScheduleDetail SD
     LEFT JOIN ##HolidayFilterTbl HF ON SD.Scheduledate = HF.Testdate
WHERE(ScheduleDate = testdate)
     AND
     /* If you wish to stick with using a CASE Expression within the WHERE Clause set the the CASE Expression equal to 'something'.  I usually stick with 1 or 0 'true/false'.
      |  You simply have to create your own True/False evaluation.  You can add your logic checks within a CASE Expression within
      |  the WHERE Clause and when your logic is TRUE THEN return 1.  Basically you are saying when 1 = 1 then return Record.
     */
     1 = 
     Case 
      WHEN HF.IsHoliday = 1 AND makeup = 0 THEN
        CASE WHEN (overtime = 1 OR overtime = 0) THEN 1 END /* Return 1 here to evaluation to TRUE */
      ELSE      
        0 /* You can add another CASE here if needed and when the condition you write in evaluations to 1 'true' return record */
      END
     
     AND
     DOW = 5

ORDER BY ActivityStartTime;

There are a few reasons I've used CASE Expressions within a WHERE Clause over using AND/ORs. Just one minor reason is it allows me to contain and organize logic in a WHERE Clause inside CASE Expressions rather than having multiple AND/ORs all nested together. I've also found that using CASE Expressions in the WHERE Clause is useful when encountering Dynamic queries that accept variables to be later inserted into the SQL before being sent to the database for processing. In the case of using Dynamic SQL there are times when a CASE Statement MUST be used due to the fact that there could be data that is being compared against in the WHERE clause that is NOT a column.field value but a hardcoded value that is compared to perhaps a user selection or status (as examples)... it might be a static value passed in via the application which is how my web application works that I support which is why I bring it up.

Basically it's good to know how to use a CASE Expression in a WHERE Clause as there are some cases when the ONLY way to evaluate certain data is by using a CASE Expression .

I have no data to test this against and that's not the point. The point of my answer is to simply provide to you an alternative to the existing answer. In my opinion this logic is basic and the already provided answer is the correct one however my answer is to demonstrate how you could go about using a CASE in a WHERE Clause.

If interested see this SO Post for the differences between a CASE Statement vs a CASE Expression however know that this terminology slightly differs between databases.

As an example of this... SQL Server refers to these as Simple vs Searched but refers to all of it as a CASE Expression. Therefore a CASE Expression can either be a Simple or a Searched CASE that can be used within a Statement.

using case statement in a where clause

2 Answers