CODEWITHSUNDEEP
sql-serversql-server-2012
jhowe
jhowe

Reputation: 10828

SQL divide by zero error nullif not working

I have an equation 

( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / 1000000 ) ) - 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) / 10 ) ) - 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? Thanks!

This also gives me divide by zero error

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

Upvotes: 0

Views: 292

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I believe this is because of types. You have nested devision operations and if the type of columns are int and the values get less then 1000000 then you are getting 0 as a result of inner operation. When you change to 10 obviously you just don't get 0 since multiplication result is greater then 10 in inner operation:

This will throw an error:

select 2 / (1 / 10)

Workaround is just to multiply inner result to 1.0 to make result of numeric type:

select 2 / (1 * 1.0 / 10)

Upvotes: 2

trincot
trincot

Reputation: 350290

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

Upvotes: 1

Related Questions