CODEWITHSUNDEEP
sqlsql-servert-sqlselectsql-null
phew
phew

Reputation: 19

Where to use NULLIF inside of a CASE WHEN?

I'm getting a divide by zero error when performing the below code. I have tried a NULLIF after the ELSE, but I am encountering the same error message: 

SELECT 
    country_IBS
    , Sku
    , LTM_Sales_USD
    , PTM_Sales_USD
    , LTM_Cost_USD
    , (CASE WHEN -([LTM_Sales_USD]-[PTM_Sales_USD])/([LTM_Sales_USD]+[PTM_Sales_USD]) IS NULL
    THEN 0
    ELSE -([LTM_Sales_USD]-[PTM_Sales_USD])/([LTM_Sales_USD]+[PTM_Sales_USD])  END  +1)/2 AS "Sales Growth % (scaled)"  
FROM #tempSalesChange

Upvotes: 0

Views: 436

Answers (2)

GMB
GMB

Reputation: 222702

If suspect that you want:

(1 - ([LTM_Sales_USD] - [PTM_Sales_USD]) / NULLIF([LTM_Sales_USD] + [PTM_Sales_USD], 0) 
) / 2 AS [Sales Growth % (scaled)]

That is: the divisor should be wrap in NULLIF(..., 0). This avoids the division by zero error - in that case, the whole computation returns NULL instead.

Upvotes: 3

Thom A
Thom A

Reputation: 96028

Wrap the divider in the NULLIF:

{Expression 1} / NULLIF({Expression 2},0)

That will mean that if the divider is 0 then NULL will be returned, and the divide by zero error will be avoided.

In your case, that's ([LTM_Sales_USD]+[PTM_Sales_USD]).

Upvotes: 3

Related Questions