Solved

Divide by zero error encountered.

Posted on 2009-05-08
609 Views
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,

City,

State,

ZipCode,

Country,

CustPO,

Routing

FROM

OrderVolume

Group By

Wave,

PktCtrlNbr,

PickArea,

Lane,

LaneId,

LaneInt,

Wave,

Shipto,

Customer,

City,

State,

ZipCode,

Country,

CustPO,

Routing
``````
0
Question by:Jeff Geiselman

LVL 75

Expert Comment

ID: 24340397
Change each of the case statement having a '/' like
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

0

LVL 3

Expert Comment

ID: 24340413
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?
0

LVL 59

Accepted Solution

Kevin Cross earned 500 total points
ID: 24340568
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,

City,

State,

ZipCode,

Country,

CustPO,

Routing

FROM

OrderVolume

Group By

Wave,

PktCtrlNbr,

PickArea,

Lane,

LaneId,

LaneInt,

Wave,

Shipto,

Customer,

City,

State,

ZipCode,

Country,

CustPO,

Routing
``````
0

LVL 1

Author Closing Comment

ID: 31579657
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.
0

LVL 59

Expert Comment

ID: 24355157
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

Happy coding!

Kev
0

Featured Post

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…