Barnold - 8 months ago 64

SQL Question

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

Answer

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
```

Source (Stackoverflow)