Solved

Divide by zero error encountered.

Posted on 2009-05-08
5
612 Views
Last Modified: 2012-05-06
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

Open in new window

0
Comment
Question by:Jeff Geiselman
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
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

by:xbrady
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

by:
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,

                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

Open in new window

0
 
LVL 1

Author Closing Comment

by:Jeff Geiselman
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

by:Kevin Cross
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

But glad that helped.

Happy coding!

Kev
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server DeDupe Table 3 35
Passing value to a stored procedure 8 89
Analysis of table use 7 40
Query to Add Late Tolerance 10 59
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 …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now