CASE is a conditional statement that performs logical tests and returns appropriate values. The CASE function evaluates expression, compares it to a sequence of values and returns a numeric result. When a value that matches expression is found, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned.
CASE is used for complex conditional expressions that contain three or more conditions. Following the CASE keyword, conditions and outcomes follow the keywords WHEN and THEN, respectively. If none of the WHEN conditions are met, the outcome following ELSE is returned. Outcomes can be numerical values or arithmetic operations. All CASE expressions conclude with the END keyword.
Avoid using nested CASE statements in metrics. A CASE statement forces calculations to be done for every single attribute that meets that condition. Anything that does not match the CASE conditions reaches its ELSE clause and has to be processed as well.
SELECT CASE WHEN … THEN …, WHEN… THEN… ELSE… END
SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END
SELECT CASE WHEN activity_type IN( email , meeting ) THEN 1, WHEN activity_type IN( phone_call , meeting ) THEN 2 ELSE 0 END
SELECT CASE WHEN SUM( Amount ) > SUM( Lost ) AND SUM( Amount ) – SUM( Lost ) > 100000 THEN 2, WHEN SUM( Amount ) > SUM( Lost ) AND SUM( Amount ) – SUM( Lost ) < 100000 THEN 1 ELSE 0 END