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 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
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
@KenJ Looks like you're right. My psychic skills are lacking tonight...
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)
