Solved

Division by Zero error in SQL Stored procedure

Posted on 2007-11-19
14
238 Views
Last Modified: 2008-02-01
I'm getting a division by 0 error in my SQL stored procedure but to me although the code should be ignoring zero's it isn't. I tested my theory by changing the "set netprem = 0" to "set netprem =
1" and running it and the error didn't occur but of course it skewed
the numbers so I can't fix it that way. Can anyone help?

update #LossRatio
set netprem = 0
where netprem is null

update #Details_Paid
set LossRatio=
(
select coalesce(((lr.dp_clmamt / lr.netprem) * 100),0)  <--- this generates a division by zero
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and lr.plancode = #Details_Paid.plancode
and lr.clntid = #Details_Paid.clntid
and lr.netprem > 0  <--- one would THINK that this would keep it from erroring out but it isn't
)
0
Comment
Question by:boukaka
  • 7
  • 7
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312260
please try this:


update #Details_Paid
set LossRatio=
(
select case lr.netprem = 0 then 0 else when coalesce(((lr.dp_clmamt / lr.netprem) * 100),0) end <--- this generates a division by zero
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and lr.plancode = #Details_Paid.plancode
and lr.clntid = #Details_Paid.clntid
and lr.netprem > 0  <--- one would THINK that this would keep it from erroring out but it isn't
)

Open in new window

0
 

Author Comment

by:boukaka
ID: 20312297
hmm... now I'm getting a syntax error

Msg 102, Level 15, State 1, Procedure RptPlanLossRatio, Line 2108
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure RptPlanLossRatio, Line 2108
Incorrect syntax near 'lr'.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312487
sorry, I had levf the keyword when on the wrong place:

update #Details_Paid
set LossRatio=
(
select case when lr.netprem = 0 then 0 else coalesce(((lr.dp_clmamt / lr.netprem) * 100),0) end 
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and lr.plancode = #Details_Paid.plancode
and lr.clntid = #Details_Paid.clntid
and lr.netprem > 0  <--- one would THINK that this would keep it from erroring out but it isn't
)

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:boukaka
ID: 20312521
I think I needed some brackets too.. this doesn't cause an error.. it does the same thing right? By the way, thank you so much for helping, this is an extremely urgent project that was due 3 wks ago <eek>

select case when lr.netprem = '0' then '0'
else (select coalesce(((lr.dp_clmamt / lr.netprem) * 100),0)
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and  lr.plancode    = #Details_Paid.plancode
and  lr.clntid      = #Details_Paid.clntid
and  lr.netprem > 0)
end
0
 

Author Comment

by:boukaka
ID: 20312561
I'm running the report now- it takes about 45 minutes and if it doesn't give me the division by zero error (ALL fingers and toes crossed), the points are all yours!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312641
>when lr.netprem = '0' then '0'
note: if the field is numerical data type, don't use quotes:
when lr.netprem = 0 then 0
0
 

Author Comment

by:boukaka
ID: 20313094
OK, I'll change that.. I'm also getting this error now.. could that be the quotes I stuck in?

Msg 4104, Level 16, State 1, Procedure RptPlanLossRatio, Line 2103
The multi-part identifier "lr.netprem" could not be bound.
0
 

Author Comment

by:boukaka
ID: 20313147
By the way - I've now changed it to this:

update #Details_Paid
set LossRatio=
(      
select case when lr.netprem = 0 then 0
else (select coalesce(((lr.dp_clmamt / lr.netprem) * 100),0)
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and  lr.plancode    = #Details_Paid.plancode
and  lr.clntid      = #Details_Paid.clntid
and  lr.netprem > 0)
end            
)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313159
you are missing the table in the join for the "lr" alias
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313171

update #Details_Paid
set LossRatio=
(      
select case when lr.netprem = 0 then 0
else coalesce(((lr.dp_clmamt / lr.netprem) * 100),0) end
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and  lr.plancode    = #Details_Paid.plancode
and  lr.clntid      = #Details_Paid.clntid
and  lr.netprem > 0
)

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20313181
or this:


update d
set LossRatio= case when lr.netprem = 0 then 0
else coalesce(((lr.dp_clmamt / (lr.netprem * 1)) * 100),0) end
from #Details_Paid dp
join #LossRatio lr
  ON lr.year_group = dp.year_group
and  lr.plancode    = dp.plancode
and  lr.clntid      = dp.clntid
and  lr.netprem > 0

Open in new window

0
 

Author Comment

by:boukaka
ID: 20313184
AH HA... so if I do this it should work right? (sorry for the extended question)

update #Details_Paid
set LossRatio=
(      
select case when #LossRatio.netprem = 0 then 0
else (select coalesce(((lr.dp_clmamt / lr.netprem) * 100),0)
from #LossRatio lr
where lr.year_group = #Details_Paid.year_group
and  lr.plancode    = #Details_Paid.plancode
and  lr.clntid      = #Details_Paid.clntid
and  lr.netprem > 0)
end
            
)
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20313216
I am never 100% sure when sql server will "see the / 0 " or not when inside case statements...

this is why I introduced the "trick" in the above suggestion:
<...>lr.dp_clmamt / (lr.netprem * 1) <...>

this should make sure that the engine does not see the / 0 value directly...
0
 

Author Comment

by:boukaka
ID: 20313223
Awesome, thank you!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

816 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

10 Experts available now in Live!

Get 1:1 Help Now