question

siera_gld avatar image
siera_gld asked

Using Case Statement in Where Clause

I am trying to use a case statement in a where clause to check for a condition The condition I want is to make sure that the result set is greater than a pct specified. WHERE ISNULL(c.PRC,0) isNull(h.PRC, 0) and (CASE WHEN isNull(h.PRC, 0) = 0 THEN '1' ELSE (isNull(c.prc,0) - isNull(h.prc,0))/h.PRC) > .2
case-statementconditionalcase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
CASE in a WHERE clause is allowed, yes. However, the clause you provide above doesn't contain a properly-formed clause. You might want to rewrite it to something along the following lines: WHERE ISNULL(c.PRC,0) isNull(h.PRC,0) AND = CASE WHEN isNull(h.Prc,0) = 0 THEN 1 ELSE IsNull(c.Prc,0) - isnull(h.prc,0))/h.prc) > .2 END for more on CASE
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think .2 might be the and there are a couple unmatched right parentheses WHERE isNull(c.PRC, 0) isNull(h.PRC, 0) AND .2 < CASE WHEN ISNULL(h.PRC, 0) = 0 THEN 1 ELSE (ISNULL(c.PRC, 0) - ISNULL(h.PRC, 0))/h.PRC END
1 Like 1 ·
Thank you Thomas - the case statement is originally in my select as CASE WHEN isNull(h.PRC, 0) = 0 THEN '1' ELSE (isNull(c.prc,0) - isNull(h.prc,0))/h.PRC END as PCT_CHG so I need to evaluate whether or not my case statement is larger than 20%... in essence this is the something i want to compare > .20
0 Likes 0 ·
I got my Parenthesis all crossed - but works fine now!! Thanks WHERE ISNULL(c.PRC,0) isNull(h.PRC, 0) and (CASE WHEN isNull(h.PRC, 0) = 0 THEN '1' ELSE ((isNull(c.prc,0) - isNull(h.prc,0))/h.PRC) END) > .2
0 Likes 0 ·
@KenJ Looks like you're right. My psychic skills are lacking tonight...
0 Likes 0 ·
@siera_gld - glad to be of help.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Beware of the performance implications by this where clause... this is not a searchable argument and will cause a scan, (table, clustereded index or index scan) and it will work for few records. But try to this on a table with 500 000 000 records. You should try to add a searchable argument to your query (and yes, and functions like ISNULL are not searchable either)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.