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.