david.jack (10/28/2016)
ScottPletcher (10/27/2016)
Seems more confusing than the natural CASE statement to me:SUM(CASE WHEN VAL1 <> 0 THEN VAL1 ELSE VAL2 END)
VAL1 can return NULL unfortunately so using a comparison operator won't work. We did have the code saying:
SUM(CASE WHEN (VAL1 = 0 or VAL1 IS NULL)....
It doesn't matter if the field can be NULL if you set up your CASE expression correctly. Scott specifically set it up so that FALSE and UNKNOWN (NULL) values were grouped together, and therefore fell under the ELSE clause taking advantage of the three-value logic. You set it up so that TRUE and UNKNOWN needed to be grouped together, so you specifically had to test for NULL, thereby forcing the three-value logic into a two-value system.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA