sql divide by zero error

Posted on

Question :

I have an equation

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) 
    / NULLIF(1000000, 0))) - 1) * 100 AS FibreScrapFactor

This works for the example order that I am looking at. However if i take out the where clause (so i have all orders) i get divide by zero error.

If I limit the / 1000000 to 10 which i then have

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) 
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor

This works for all orders, but is incorrect I need to divide by a million. How can I get this to work? The columns are numeric(32, 16).

Answer :

Since your data types are already decimal types, perhaps you need to remove NULL values with a WHERE clause instead of coalescing them to 0, which would result in the “divide by zero” error.

Something like:

SELECT ((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) 
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor
FROM  <...>
WHERE PO.ActualQty IS NOT NULL
    AND P.QuantityPO IS NOT NULL

Hi I ended up doing this… this resolved my issue…

CASE WHEN (PO.ActualQty * P.QuantityPO / 1000000) <> 0 THEN
    ( P.RealisedConsumption / ( PO.ActualQty * P.QuantityPO / 1000000 ) - 1 ) * 100 
END AS FibreScrapFactor

Leave a Reply

Your email address will not be published.