Search code examples
sqlcasecoalesce

COALESCE on calculated column to remove NULL

The formula below works fine now. It gives a result of 1 when the calculation is above 4. The problem I am running into is trying to replace the NULL with COALESCE / zero. I have a tried a few options and the syntax keeps breaking.

CASE 
    WHEN CEILING(ORDER_QTY * 1. / BOUM * 1.) > 4 
        THEN 1
END AS 'ABOVE TARGET'
Solution

  • The ELSE portion of a CASE expression defaults to NULL if you don't state something else explicitly.

    You should choose what you want to have instead of NULL, like so:

    CASE 
    WHEN CEILING(ORDER_QTY * 1. / BOUM * 1.) > 4 
        THEN 1
    ELSE 0 --Here
END AS 'ABOVE TARGET'

    Either that or you should put your logic in a filter if you a trying to only see rows that are 'above target', ie.:

    WHERE CEILING(ORDER_QTY * 1. / BOUM * 1.) > 4