Division by Zero error in SQL Stored procedure
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
)
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
)
ASKER
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'.
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'.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
)ASKER
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
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
ASKER
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!
>when lr.netprem = '0' then '0'
note: if the field is numerical data type, don't use quotes:
when lr.netprem = 0 then 0
note: if the field is numerical data type, don't use quotes:
when lr.netprem = 0 then 0
ASKER
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.
Msg 4104, Level 16, State 1, Procedure RptPlanLossRatio, Line 2103
The multi-part identifier "lr.netprem" could not be bound.
ASKER
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
)
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
)
you are missing the table in the join for the "lr" alias
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
)
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 > 0ASKER
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
)
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
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, thank you!
Open in new window