Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

Question

I have below query, which is giving error of Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
I think due to null value ,it is generating error, please guide me how to resolve it

Second thing is that, it is taking much time in executing or retrieving data

;with cte1 as(

select  S.Secnam ,


ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY1,ISNULL(sum(b1.Bweight),0)Bigbale_Weight1,
ISNULL(SUM(P.prdQTY),0)Smallbale_QTY1,ISNULL(SUM(case when P.prdQTY is null
 then 0 else  i.weight end ),0)Small_Bale_weight1



from ItemMasterFile I  inner join Sections S on S.SecID=i.SecID
left outer join Probale P on P.Codeitem=I.CodeItem
  LEFT OUTER JOIN Bigbalprd b1 ON i.CodeItem = b1.CodeItem

where (P.DelID is null and P.FID=1 and P.Entrydate BETWEEN @StartDate AND @Enddate or P.EntryDate is null ) 
  and
  (b1.DelID is null and  b1.FID=1 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )

group by s.Secnam
)
,cte2 as(

select  S.Secnam ,
ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY2,ISNULL(sum(b1.Bweight),0)Bigbale_Weight2,
ISNULL(SUM(P.prdQTY),0)Smallbale_QTY2,ISNULL(SUM(case when P.prdQTY is null
 then 0 else  i.weight end ),0)Small_Bale_weight2

from ItemMasterFile I  inner join Sections S on S.SecID=i.SecID
left outer join Probale P on P.Codeitem=I.CodeItem
  LEFT OUTER JOIN Bigbalprd b1 ON i.CodeItem = b1.CodeItem
--where P.EntryDate='2021-10-26' and P.DelID is null and P.FID=1
where (P.DelID is null and P.FID=2 and P.Entrydate BETWEEN @StartDate AND @Enddate or P.EntryDate is null ) 
  and
  (b1.DelID is null and  b1.FID=2 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )

group by s.Secnam   

),  cte1w as (
  SELECT secnam,Small_Bale_weight1, Bigbale_Weight1,

  ( SELECT SUM(val)
  FROM (VALUES (Bigbale_Weight1),
  (Small_Bale_weight1 )
  )AS value(val)
  ) as Total_Weight
  FROM cte1
  ),  cte2w as (
  SELECT secnam,Small_Bale_weight2, Bigbale_Weight2,

  ( SELECT SUM(val)
  FROM (VALUES (Bigbale_Weight2),
  (Small_Bale_weight2 )
  )AS value(val)
  ) as Total_Weight2
  FROM cte2
  )


select isnull(cte1w.Secnam,cte2w.Secnam) Section,cte1w.Small_Bale_weight1 Small_Bale_weight ,cte2w.Small_Bale_weight2 Small_Bale_weight1,
cte1w.Bigbale_Weight1 Bigbale_weigh,cte2w.Bigbale_Weight2 Bigbale_weigh1,Total_Weight,Total_Weight2,
sum(Total_Weight+Total_Weight2) Packed,

cast((Small_Bale_weight1)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) ))AS numeric(10,2))as percentagesb1 ,
cast((Small_Bale_weight2)*100.0/(sum(Total_Weight2)over(PARTITION  BY (1) ))AS numeric(10,2))as percentagesb2 ,
cast((Bigbale_Weight1)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) ))AS numeric(10,2))as percentagebb1 ,
cast((Bigbale_Weight2)*100.0/(sum(Total_Weight2)over(PARTITION  BY (1) ))AS numeric(10,2))as percentagebb2 ,
cast((Small_Bale_weight1+Small_Bale_weight2)*100.0/(sum(Total_Weight2+Total_Weight)over(PARTITION  BY (1) ))AS numeric(10,2))as SBPerTotal ,
cast((Bigbale_Weight1+Bigbale_Weight2)*100.0/(sum(Total_Weight2+Total_Weight)over(PARTITION  BY (1) ))AS numeric(10,2))as BBPerTotal ,


(isnull(Small_Bale_weight1,0)*100.0)/nullif(sum(Bigbale_Weight1+Small_Bale_weight1),0)as SBPer1 ,
(isnull(Small_Bale_weight2,0)*100.0)/nullif(sum(Small_Bale_weight2+Bigbale_Weight2),0)as SBPer2,
(Small_Bale_weight1+Small_Bale_weight2)*100.0/nullif(sum(Small_Bale_weight1+Small_Bale_weight2+Bigbale_Weight1),0)as SBperT
,cast((Small_Bale_weight1+Bigbale_Weight1)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) ))AS numeric(10,2))as Ragper1 ,
cast((Small_Bale_weight2+Bigbale_Weight2)*100.0/(sum(Total_Weight2)over(PARTITION  BY (1) ))AS numeric(10,2))as Ragper2 ,
cast((Small_Bale_weight1+Bigbale_Weight1+Small_Bale_weight2+Bigbale_Weight2)*100.0/(sum(Total_Weight+Total_Weight2)over(PARTITION  BY (1) ))AS numeric(10,2))as RagperTotal 



from cte1w full join cte2w on cte1w.Secnam=cte2w.Secnam

group by cte1w.Secnam,cte2w.Secnam,cte1w.Small_Bale_weight1  ,cte2w.Small_Bale_weight2 ,
cte1w.Bigbale_Weight1 ,cte2w.Bigbale_Weight2 ,Total_Weight,Total_Weight2

Answer 1

You need to put NULLIF function to all expressions that use /, not into only some of them. You can see, for example:

cast((Small_Bale_weight1+Small_Bale_weight2)*100.0/(sum(Total_Weight2+Total_Weight)over(PARTITION BY (1) ))AS numeric(10,2))as SBPerTotal ,

it should be changed to

cast((Small_Bale_weight1+Small_Bale_weight2)*100.0/NULLIF(sum(Total_Weight2+Total_Weight),0) over(PARTITION BY (1) ))AS numeric(10,2))as SBPerTotal ,

Also, it may help to move SUM(TotalWeight) OVER () -- no need to partition into a separate CROSS APPLY

e.g.
CROSS APPLY (select NULLIF(sum(TotalWeight),0) over() as AllTotalWeight, NULLIF(sum(TotalWeight2),0) over() as AllTotalWeight2) X and use these two expressions. Also, I haven't tested the exact syntax, it may be that you need to put NULLIF for the whole expression.

Answer 2

Hi，@Analyst_SQL

There are three ways to deal with this situation. The first is to use the NULLIF function. The second is to set ARITHABORT off.
You can use this：

SET ARITHABORT OFF   
SET ANSI_WARNINGS OFF  

The third is to use case when to avoid errors.According to your code, it may be The problem is that the sum of the Total_Weight+Total_Weight2 partition may be 0. You can try to modify it according to naomi's method. If an error occurs, please provide us with some test data, and we can help you calculate it.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Divide by zero error encountered.

A pretty clear error message, isn't it?

I think due to null value

No, not by NULL but by 0 = zero, as the error message say.

please guide me how to resolve it

Use a CASE condition to avoid divison by zero, like

... case when divider = 0 then null else value / divider end as Result