?
Solved

Division by Zero error in SQL Stored procedure

Posted on 2007-11-19
14
Medium Priority
?
259 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 143

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 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 143

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 143

Expert Comment

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

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

829 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