CODEWITHSUNDEEP
sqlt-sql
Barnold
Barnold

Reputation: 130

SQL Divide by zero error encountered cannot see where to put NULLIF

I am writing a query and getting a divide by zero error. I've had this error before with seperate queries but can usually fix it with a NULLIF statement.

This time I am really struggling to get rid of the error with NULLIF, I'm not sure if this is because I have been putting it in the right place or not.

Here is my code without any NULLIF statements:

Select
DATEPART(Year,C.CreatedDate),
DATENAME(Month,C.CreatedDate),
CASE WHEN D.UnitID = 'BOX' THEN
    D.QtyAffected * I.BCT_Product_Qty / (1 / (convert(decimal(18,3),T.Width) / 1000 * convert(decimal(18,3),T.Height) / 1000))
ELSE
    D.QtyAffected / (1 / (convert(decimal(18,3),T.Width) / 1000 * convert(decimal(18,3),T.Height) / 1000))
End as 'M2 Affected',
C.CostReplacementTile + C.CostRedelivery + C.CostCredit + C.CostRefixing + C.CostVoucher 'Cost'

From BCTComplaintRegister C

Left Join BCTComplaintProductRegister D
On D.CaseID = C.CaseID

Left Join InventTable I
On I.ItemID = D.ItemID

Left Join BCTTileSize T
On T.TileSizeID = I.BCTTileSize

Where 
--DATEDIFF(M,C.CreatedDate,GETDATE()) <= 1
C.CaseID = 'CE23294'

The divide by zero error relates to the 2 case statements, I would just like to return zero or something if it cannot perform the division.

Can anyone help me on where to put NULLIF? Or is there a better alternative.

Thank you

Upvotes: 1

Views: 198

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You put it in the denominator:

CASE WHEN D.UnitID = 'BOX' THEN
    D.QtyAffected * I.BCT_Product_Qty /
     NULLIF(1 / (convert(decimal(18,3),T.Width) / 1000 * convert(decimal(18,3),T.Height) / 1000))
ELSE
    D.QtyAffected /
     NULLIF((1 / (convert(decimal(18,3),T.Width) / 1000 * convert(decimal(18,3),T.Height) / 1000))
END

Upvotes: 2

Related Questions