I have a query that used to work just fine and now when i run it i get the message: Divide by zero error encountered. I have checked my data and there are no zero values in the fields used as the denominator. i don't understand how i can all of a sudden start getting this message. Does anyone have any clue what could be wrong? I have added a code segment of the procedure where the error is occuring.

Select PktCtrlNbr, Lane, LaneId, LaneInt, Wave, Case When PickArea = 'UN1' Then Ceiling(Sum(PickQty)) Else 0 End As Un1Qy, Case When PickArea = 'UN2' Then Ceiling(Sum(PickQty)) Else 0 End As Un2Qy, Case When PickArea = 'NCP' Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CpnQy, Case When PickArea = 'OCP' Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CpoQy, Case When PickArea = 'NCP' Or PickArea = 'OCP' Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CPQy, Case When PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As RsvQy, Case When PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PackVol * (PickQty / PackQty))) Else 0 End As RsvVl, Case When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitWt * PickQty)) When PickArea = 'NCP' Or PickArea = 'OCP' Or PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PackWt * (PickQty / PackQty))) End As Weight, Case When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitVol * PickQty)) When PickArea = 'NCP' Or PickArea = 'OCP' Or PickArea = 'EOS' Or PickArea = 'COS' Or PickArea = 'NRS' Or PickArea = 'ORS' Then Ceiling(Sum(PackVol * (PickQty / PackQty))) End As Volume, Shipto, Customer, Address1, Address2, Address3, City, State, ZipCode, Country, CustPO, RoutingFROM OrderVolumeGroup By Wave, PktCtrlNbr, PickArea, Lane, LaneId, LaneInt, Wave, Shipto, Customer, Address1, Address2, Address3, City, State, ZipCode, Country, CustPO, Routing

I'm not sure why that might be happening in your specific case but it would probably be best if you replaced all instances of
(PickQty / PackQty)
with
CASE WHEN PackQty == 0 THEN 0.0 ELSE (PickQty / PackQty) END

Do you still get the divide by zero error if you do that?

Your issue as described by error message is caused by dividing by 0; therefore, you will have to account for that as shown. One slight tweak is that since your CASE WHEN already had an ELSE with 0, I would just do this:

Case
When PickArea = 'NCP' And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty))
Else
0
End As CpnQy

Select PktCtrlNbr, Lane, LaneId, LaneInt, Wave, Case When PickArea = 'UN1' Then Ceiling(Sum(PickQty)) Else 0 End As Un1Qy, Case When PickArea = 'UN2' Then Ceiling(Sum(PickQty)) Else 0 End As Un2Qy, Case When PickArea = 'NCP' And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CpnQy, Case When PickArea = 'OCP' And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CpoQy, Case When PickArea IN ('NCP', 'OCP') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As CPQy, Case When PickArea IN ('EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PickQty / PackQty)) Else 0 End As RsvQy, Case When PickArea IN ('EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackVol * (PickQty / PackQty))) Else 0 End As RsvVl, Case When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitWt * PickQty)) When PickArea IN ('NCP', 'OCP', 'EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackWt * (PickQty / PackQty))) End As Weight, Case When PickArea = 'UN1' Or PickArea = 'UN2' Then Ceiling(Sum(UnitVol * PickQty)) When PickArea IN ('NCP', 'OCP', 'EOS', 'COS', 'NRS', 'ORS') And IsNull(PackQty, 0) <> 0 Then Ceiling(Sum(PackVol * (PickQty / PackQty))) End As Volume, Shipto, Customer, Address1, Address2, Address3, City, State, ZipCode, Country, CustPO, RoutingFROM OrderVolumeGroup By Wave, PktCtrlNbr, PickArea, Lane, LaneId, LaneInt, Wave, Shipto, Customer, Address1, Address2, Address3, City, State, ZipCode, Country, CustPO, Routing

Thanks that works great except I had to add PackQty to the Group By clause as it was not in an aggregate clause. Thanks for showing me how to use the IN clause that will save many keystrokes with large case statements.

CEILING(SUM(Case When PickArea = 'OCP' And IsNull(PackQty, 0) <> 0 Then PickQty / PackQty Else 0 End)) As CpoQy

OR...

Case When PickArea = 'OCP' Then Ceiling(Sum(Case When IsNull(PackQty, 0) <> 0 Then PickQty / PackQty)) Else 0 End As CpoQy

But glad that helped.

Happy coding!

Kev

0

Featured Post

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Case

When PickArea = 'NCP' Then Ceiling(Sum(PickQty / PackQty))

Else

0

End As CpnQy

to

Case

When PickArea = 'NCP' Then CASE WHEN PackQty =0 THEN NULL ELSE Ceiling(Sum(PickQty / PackQty)) END

Else

0

End As CpnQy