SQL Stored Procedure Divide by zero error encountered.

Question

Trying to figure out why my stored procedure is giving me a "divide by zero" error and I've narrowed it down to this area. If I comment the follow lines out the query runs successfully. I am not sure why though.

            PartReceived.QuantityReceived / 
            (CASE 
                WHEN PurchaseOrderItem.QuantityOrdered IS NULL THEN PurchaseOrderItem.QuantityOrdered
                WHEN PurchaseOrderItem.QuantityOrdered = 0 THEN PurchaseOrderItem.QuantityOrdered
                ELSE PurchaseOrderItem.QuantityOrdered
            END) * 100.0 AS PercentageReceived

I do get this warning before the error but I've done much of what any google search has lead me to do. I've tried using IFNULL instead of IS NULL THEN but that didn't seem to work either.

Warning: Null value is eliminated by an aggregate or other SET operation.

Answer 1

You can avoid divide-by-zero by using NULLIF():

        PartReceived.QuantityReceived / 
        NULLIF(CASE WHEN PurchaseOrderItem.QuantityOrdered IS NULL THEN PurchaseOrderItem.QuantityOrdered
                    WHEN PurchaseOrderItem.QuantityOrdered = 0 THEN PurchaseOrderItem.QuantityOrdered
                    ELSE PurchaseOrderItem.QuantityOrdered
                END), 0) * 100.0 AS PercentageReceived

This returns the result as NULL rather than generating an error.

Actually, your logic would much more easily be written as:

        (PartReceived.QuantityReceived / 
         NULLIF(PurchaseOrderItem.QuantityOrdered, 0)
        ) * 100.0 AS PercentageReceived

The CASE isn't doing anything. All three THEN clauses return the same value.