Comparison operator use in Case statement using SQL Server-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

Try this:

WHERE 
    ((@isActual = 0 AND a.TimeStamUtc > @endDateUtc) 
     OR a.TimestampUtc <= @endDateUtc END)
    ...

This is the way how to use conditions in WHERE clauses. https://stackoverflow.com/a/18629782/11305029