Solved

Division by Zero error in SQL Stored procedure

Posted on 2007-11-19
14
234 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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
 

Author Comment

by:boukaka
Comment Utility
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
Comment Utility
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]
Comment Utility
>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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:boukaka
Comment Utility
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]
Comment Utility
you are missing the table in the join for the "lr" alias
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility

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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Awesome, thank you!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Word Template Mail merge with vb.net 4 38
Library to convert HTML to PDF. 8 42
Updating variable table 9 15
SQL JOIN 6 27
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

763 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

9 Experts available now in Live!

Get 1:1 Help Now