Case in a where clause?

Question

Case in a where clause?

jbalbo

SSCertifiable

Points: 7583
More actions
February 5, 2019 at 12:12 pm

#379286

Hi
Can someone let me know what I;m doing wrong here?
I thought this was correct but keep getting a syntax error
Thanks
WHERE([Admission Date] IS NOT NULL)
   and
     Case when @Parameter = 'A'
     then
      ([Close Date] IS NULL)
       AND ([Discharge Date] IS NULL)
   else
     ([Close Date] IS not NULL)
       AND ([Discharge Date] IS not NULL)
   end
;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101105 More actions · Answer 1

CASE must evaluate to a single value. For this, you need to use "standard" WHERE clause and/ors:

WHERE([Admission Date] IS NOT NULL)
and
(@Parameter = 'A' and [Close Date] IS NULL and [Discharge Date] IS NULL)
or
((@Parameter is null or @Parameter <> 'A') and [Close Date] IS not NULL and [Discharge Date] IS not NULL)
;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DesNorton SSC-Insane Points: 24387 More actions · Answer 2

jbalbo - Tuesday, February 5, 2019 12:12 PM
Hi
Can someone let me know what I;m doing wrong here?
I thought this was correct but keep getting a syntax error
Thanks
WHERE([Admission Date] IS NOT NULL)
   and
     Case when @Parameter = 'A'
     then
      ([Close Date] IS NULL)
       AND ([Discharge Date] IS NULL)
   else
     ([Close Date] IS not NULL)
       AND ([Discharge Date] IS not NULL)
   end
;

This should get you what you are looking for
WHERE ([Admission Date] IS NOT NULL) AND CASE WHEN @Parameter = 'A' AND ([Close Date] IS NULL) AND ([Discharge Date] IS NULL) THEN 1 WHEN @Parameter <> 'A' AND ([Close Date] IS NOT NULL) AND ([Discharge Date] IS NOT NULL) THEN 1 ELSE 0 END = 1

How to post data/code on a forum to get the best help.

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

ScottPletcher - Tuesday, February 5, 2019 12:35 PM
CASE must evaluate to a single value. For this, you need to use "standard" WHERE clause and/ors:
WHERE([Admission Date] IS NOT NULL)
and
(@Parameter = 'A' and [Close Date] IS NULL and [Discharge Date] IS NULL)
or
((@Parameter is null or @Parameter <> 'A') and [Close Date] IS not NULL and [Discharge Date] IS not NULL)
;

Thanks Scott I totally missed that I dont know why, This works great but so doesn't DesNorton.
Nice to know both

Thanks again So much