Page tree
Skip to end of metadata
Go to start of metadata

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.

Description
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.

Syntax
SELECT CASE WHEN … THEN …, WHEN… THEN… ELSE… END
SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END

Examples
SELECT CASE WHEN activity_type IN(emailmeeting) THEN 1, WHEN activity_type IN(phone_callmeeting) 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

  • No labels