Comparison operator use in Case statement

Question

I am having some trouble with the syntax when I am trying to use the below query in SQL Server. I wanted to show WHERE clause based on condition.

This is my code:

DECLARE @isActual = 0

SELECT
    SolverRunId, PointId, TimeStampUtc, Value,
    UnitOfMeasure, Reliability
FROM
    (SELECT 
         bt.SolverRunId, bt.PointId, bt.TimeStampUtc,
         bt.Value, bt.UnitOfMeasure, bt.Reliability
     FROM 
         cte bt
     WHERE 
         bt.TimeStampUtc = bt.TargetTimeUtc

     UNION ALL

     SELECT 
         a.SolverRunId, a.PointId, a.TimeStampUtc, a.Value,
         a.UnitOfMeasure, a.Reliability
     FROM 
         cte a
     WHERE 
         -- I tried using this case but it is syntactically incorrect
         CASE 
             WHEN @isActual = 0 THEN a.TimeStamUtc > @endDateUtc 
             ELSE a.TimestampUtc <= @endDateUtc 
         END
         -- instead of this. I wanted to have conditional where based on @isActual value from 0 to 1
         a.TimeStampUtc > @endDateUtc
         AND a.SolverRunId = @maxRun) x
ORDER BY 
    SolverRunId, PointId, TimeStampUtc;

I wanted to have the where condition to be evaluated based on @isActual set to true or false

2) It's better to use regular AND/OR constructions instead of case in the WHERE clause. — jarlh, Sep 29 '21 at 08:21
`CASE` returns a *scalar* value, not a boolean result. Though don't use a `CASE` **expression** in the `WHERE` anyway, stick to `AND` and `OR` statements. — Thom A, Sep 29 '21 at 08:21

score 2 · Accepted Answer · answered Sep 29 '21 at 08:27

As mentioned in the comments, don't use a CASE in the WHERE just use proper boolean logic with AND and OR clauses. In your question your variable @isActual is also missing a data type, so I have assumed it is a bit:

DECLARE @isActual bit = 0;
SELECT SolverRunId,
       PointId,
       TimeStampUtc,
       Value,
       UnitOfMeasure,
       Reliability
FROM (SELECT bt.SolverRunId,
             bt.PointId,
             bt.TimeStampUtc,
             bt.Value,
             bt.UnitOfMeasure,
             bt.Reliability
      FROM cte bt
      WHERE bt.TimeStampUtc = bt.TargetTimeUtc
      UNION ALL
      SELECT a.SolverRunId,
             a.PointId,
             a.TimeStampUtc,
             a.Value,
             a.UnitOfMeasure,
             a.Reliability
      FROM cte a
      WHERE a.TimeStampUtc > @endDateUtc
        AND a.SolverRunId = @maxRun
        AND ((@isActual = 0 AND a.TimeStamUtc > @endDateUtc)
          OR (@isActual = 1 AND a.TimestampUtc <= @endDateUtc))) x
ORDER BY SolverRunId,
         PointId,
         TimeStampUtc;

You may also want experiment with adding RECOMPILE to the OPTION clause of the above, as the query plan requirements for when @isActual has a value of 1 or 0 could be quite different.

A very good reference for the relative merits of IF, UNION, AND/OR, OPTION RECOMPILE, etc : https://www.sommarskog.se/dyn-search.html — MatBailie, Sep 29 '21 at 08:31
Thank you.. I made a mistake of using Case, when it should easily be done by And OR operator. I seriously made a silly mistake here — Running Rabbit, Sep 29 '21 at 08:46

Comparison operator use in Case statement

1 Answers1