case in HAVING statement

How can I include case in having statement:
......
HAVING case when @.korak=1440 then (T5.fromTime is not null AND T5.toTime is
not null) else
((T5.fromTime is null AND T5.toTime is null AND T5.izd_nivo is null) OR (T5.
fromTime is not null AND T5.toTime is not null)) end
This isn't working, wrong sintaks. Does anybody know the right sintaks?
Thank you,
SimonCASE is an expression, it returns a scalar value than you can compare with s
omething else, not a Boolean.
And considering that you don't have any aggregates in your HAVING clause, it
makes more sense to make it a WHERE clause. And the clause includes rows th
at have (T5.fromTime is not null AND T5.toTime is not null) anyway, whether
@.korak equals 1440 or not. So the following simplifies version should do the
job:
WHERE (T5.fromTime is not null AND T5.toTime is not null)
OR (@.korak!=1440 AND T5.fromTime is null AND T5.toTime is null AND T5.izd_ni
vo is null)
--
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:erbE0VDNFHA.3668
@.TK2MSFTNGP14.phx.gbl...
How can I include case in having statement:
.....
HAVING case when @.korak=1440 then (T5.fromTime is not null AND T5.toTime is
not null) else
((T5.fromTime is null AND T5.toTime is null AND T5.izd_nivo is null) OR (T5.
fromTime is not null AND T5.toTime is not null)) end
This isn't working, wrong sintaks. Does anybody know the right sintaks?
Thank you,
Simon|||Thank you Jacco. I have aggregates but I didn't write them here. It doesn't
matter.
But what if I have :
...
) as T5 GROUP BY T5.fromtime,T5.totime,t5.izd_nivo WITH ROLLUP
HAVING CASE WHEN @.korak=1440 then (T5.fromTime is null AND T5.toTime is null
AND T5.izd_nivo is null)
else (T5.fromTime is not null AND T5.toTime is not null) end
Regards,
Simon
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Oz$ay7DNFHA.3380@.TK2MSFTNGP15.phx.gbl...
CASE is an expression, it returns a scalar value than you can compare with s
omething else, not a Boolean.
And considering that you don't have any aggregates in your HAVING clause, it
makes more sense to make it a WHERE clause. And the clause includes rows th
at have (T5.fromTime is not null AND T5.toTime is not null) anyway, whether
@.korak equals 1440 or not. So the following simplifies version- should do th
e job:
WHERE (T5.fromTime is not null AND T5.toTime is not null)
OR (@.korak!=1440 AND T5.fromTime is null AND T5.toTime is null AND T5.izd_ni
vo is null)
--
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:erbE0VDNFHA.3668
@.TK2MSFTNGP14.phx.gbl...
How can I include case in having statement:
......
HAVING case when @.korak=1440 then (T5.fromTime is not null AND T5.toTime is
not null) else
((T5.fromTime is null AND T5.toTime is null AND T5.izd_nivo is null) OR (T5.
fromTime is not null AND T5.toTime is not null)) end
This isn't working, wrong sintaks. Does anybody know the right sintaks?
Thank you,
Simon|||As Jacco suggested, in your example you can use WHERE rather than
HAVING - although the logic is the same in either case:
WHERE (@.korak=1440 AND T5.fromtime IS NULL AND T5.totime IS NULL AND
T5.izd_nivo IS NULL)
OR (@.korak<>1440 AND T5.fromtime IS NOT NULL AND T5.totime IS NOT
NULL)
Try also using UNION, which may give a better execution plan:
SELECT ...
WHERE @.korak=1440
AND T5.fromtime IS NULL
AND T5.totime IS NULL
AND T5.izd_nivo IS NULL
UNION ALL
SELECT ...
WHERE @.korak<>1440
AND T5.fromtime IS NOT NULL
AND T5.totime IS NOT NULL
David Portas
SQL Server MVP
--|||To use CASE in criteria, I use the following:
WHERE (1=Case when [your criteria = true] then 1 else 0 end)
HTH
Fred
"simon" wrote:

> How can I include case in having statement:
> ......
> HAVING case when @.korak=1440 then (T5.fromTime is not null AND T5.toTime i
s not null) else
> ((T5.fromTime is null AND T5.toTime is null AND T5.izd_nivo is null) OR (T
5.fromTime is not null AND T5.toTime is not null)) end
> This isn't working, wrong sintaks. Does anybody know the right sintaks?
> Thank you,
> Simon
>