[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

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

Open in new window

0
Jeff Geiselman
Asked:
Jeff Geiselman
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
  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
 
xbradyCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Jeff GeiselmanAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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

Veeam Disaster Recovery in Microsoft Azure

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now