Jeff Geiselman
asked on
Divide by zero error encountered.
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,
Routing
FROM
OrderVolume
Group 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?
(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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Or move the entire CASE statement inside the SUM.
Therefore...
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
Therefore...
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
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