|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-15 : 12:49:09
|
Paula writes "I have a stored procedure which produces a report in response to an asp request. I am new at writing SQL--most of my experience has been with Microsoft Access. I have a row of data where I am calculating a percentage of supervisors eligible and not eligible for retirement within a specific organization--at the same time I show the percentage of non-supervisors eligible and not eligible for the same organization. I have encountered a problem where an organization has no supervisors, so when I try to calculate the percentage I get this divide by zero error. I changed my statement to allow for a zero:
'pctsupvelig' = case totalsupv
when 0 then 0
else convert(decimal(6,2),sum((supv ) * 1.000 / totalsupv * 1.000 ) * 100)
end
I still received the error and processing stopped at that row. So then I added the following statement:
SET ANSI_WARNINGS OFF
Well, this statement allowed the process to continue, but I still received the error message.
Is there anyway to get around this? I've been searching for an answer for two days and have not been able to resolve it. Can you help? I am using SQL Server 7.0. Thank you in advance.
Paula
Here is the whole procedure:
CREATE PROCEDURE [sp_getretirementeligiblesSupvbur]
@retdate1 smalldatetime,
@bureau1 varchar(2)
as
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
begin
create table #totaleligsupv
(bureau varchar(6), agency_code varchar(2), o2 varchar(2), supv int, nonsupv int, totalsupv int, totalnon int)
insert into #totaleligsupv
exec colbyp.sp_findretirementeligssupvbur @retdate = @retdate1, @bureau = @bureau1
select Bureau, agency_code, o2, totalsupv as 'Total Supv',
sum(totalsupv - supv) as 'Supv Not Elig',
sum(supv) as 'Supv Elig', 'pctsupvelig' =
case totalsupv
when 0 then 0 else convert(decimal(6,2),sum((supv ) * 1.000 / totalsupv * 1.000 ) * 100) end,
totalnon as 'Total Non Supv',
sum(totalnon - nonsupv) as 'Non Supv Not Elig',
sum(nonsupv) as 'Non Supv Elig', pctnonsupvelig =
case totalnon
when 0 then 0 else
convert(decimal(6,2),sum((nonsupv) * 1.000 / totalnon * 1.000) * 100) end ,
sum(totalsupv + totalnon) as 'Total'
from #totaleligsupv
group by bureau, agency_code, o2, supv, nonsupv, totalsupv, totalnon
create table #totaleligsgrand
(totalsupvelig int, totalsupvnot int, totalsupv int, totalnonelig int, totalnonnot int, totalnon int,totalempl int)
insert into #totaleligsgrand
select sum(supv),
sum(totalsupv - supv) ,
sum(totalsupv),
sum(nonsupv),
sum(totalnon - nonsupv),
sum(totalnon),
sum(totalsupv + totalnon)
from #totaleligsupv
select sum(totalsupv),sum(totalsupvnot), sum(totalsupvelig), convert(decimal(6,2),sum((totalsupvelig * 1.000 ) / totalsupv * 1.000 ) * 100),
sum(totalnon),sum(totalnonnot),sum(totalnonelig), convert(decimal(6,2),sum((totalnonelig * 1.000) /totalnon * 1.000) * 100),
sum(totalempl)
from #totaleligsgrand
end " |
|
Divide by zero error encountered