Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Division by Zero error in SQL Stored procedure

Posted on 2007-11-19
14
Medium Priority
?
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

636 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