John Mitchell-245523 (8/24/2012)
You need to handle all eventualities once and once only in your case expression - something like this:
...CASE WHEN Interval < 1 THEN 'X'
WHEN Interval >= 1 AND Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...
John
Actually, you're wrong on both counts. You do not need to handle eventualities at all. The CASE expression will just produce a NULL if you do not handle the eventuality. I use this quite a bit, especially when the CASE expression is embedded in a COUNT(). It doesn't make sense to me to code "ELSE NULL" when it's going to do that anyhow.
Second, you do not need to make sure that each eventuality meets only one condition. The CASE expression short circuits the evaluation once a condition is met. That means that you can simplify your conditions, because you don't need to exclude previous conditions, because that condition won't even be evaluated if previous conditions are met. Your code can be simplified as follows:
...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA