Solved

Division by Zero error in SQL Stored procedure

Posted on 2007-11-19
14
241 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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).

839 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