Divide by zero error encountered. error!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Divide by zero error encountered. error!!!

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-06-12 : 04:10:38

HI All

I am creating a Stored procedure to produce some sales reports however I am getting a divide by zero error. I think it occurs in the bottom part of the query but I cant seem to handle it properly.


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
 
ALTER  PROCEDURE PW_SectorSalesPrdGrp_Summary @CurrStart int, @CurrMonth int, @PrevStart int, @PrevEnd int AS

select d.pg4 as prodgrp, b.sectorcode as sectorcode, 
Sum(CASE when salesperiod between @CurrStart and @CurrMonth then a.linesellvalue else 0 end) as CurrSales,
Sum(CASE when salesperiod between @PrevStart and @PrevEnd then a.linesellvalue else 0 end) as PreviousSales,
Sum(CASE when salesperiod between @CurrStart and @CurrMonth then a.linesellvalue - a.linecostvalue else 0 end) as CurrGPC,
Sum(CASE when salesperiod between @PrevStart and @PrevEnd then a.linesellvalue - a.linecostvalue else 0 end) as PreviousGPC
into #temp_table
from Consol..tCustMatRollup a, Consol..tCustomer b, Consol..tSAPMaterials c, IBP_PRD..tProductGroup d
where a.customerno = b.customerno and 
c.material = a.material and 
d.pg4 = c.pg4 and 
d.pg4category = 'workplace'

group by b.sectorcode, d.pg4
order by b.sectorcode,d.pg4 asc

select prodgrp, sectorcode , CurrSales, PreviousSales ,CurrGPC, PreviousGPC, 
(CurrSales/PreviousSales ) * 100 as 'Sales%',
(CurrGPC/PreviousGPC) * 100 as 'GPC%',
((CurrGPC)*100) / CurrSales as 'CurrGP%',
(((CurrGPC*100) /CurrSales) / ((PreviousGPC*100)/PreviousSales)) * 100 as 'YOY' 
from #temp_table

union 

select  prodgrp, 'Zt', sum(CurrSales), sum(PreviousSales),sum(CurrGPC),sum(PreviousGPC), 
(sum(CurrSales)/sum(PreviousSales) )*100 as 'Sales%',
(sum(CurrGPC)/sum(PreviousGPC)) *100 as 'GPC%',
((sum(CurrGPC))*100) / sum(CurrSales) as 'CurrGP%',
(((sum(CurrGPC)*100)/sum(CurrSales)) / ((sum(PreviousGPC)*100)/sum(PreviousSales))) * 100 as 'YOY' 
from #temp_table
group by prodgrp

Any help would be great!

Thanks
Gopher

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 04:47:12

It can be one or more of possible 3 fields.

1. PreviousSales
2. PreviousGPC
3. CurrSales

If unsure, add CASE WHEN PreviousSales = 0 THEN 0 ELSE a / PreviousSales END, kind of thing.

Peter Larsson
Helsingborg, Sweden

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-12 : 04:52:44

why don't you determine which part of query it occurs? show the values of the fields you are trying to compute on to give you a clearer picture

to handle, if you are dividing by zero, then do not proceed, use 'case' or nullif + coalesce

--------------------
keeping it simple...

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-06-12 : 05:15:32

can you give me an example jen?

I have added the info suggested by Peso and have even more errors:

New code is

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE PW_SectorSalesPrdGrp_Summary @CurrStart int, @CurrMonth int, @PrevStart int, @PrevEnd int AS

select d.pg4 as prodgrp, b.sectorcode as sectorcode,
Sum(CASE when salesperiod between @CurrStart and @CurrMonth then a.linesellvalue else 0 end) as CurrSales,
Sum(CASE when salesperiod between @PrevStart and @PrevEnd then a.linesellvalue else 0 end) as PreviousSales,
Sum(CASE when salesperiod between @CurrStart and @CurrMonth then a.linesellvalue - a.linecostvalue else 0 end) as CurrGPC,
Sum(CASE when salesperiod between @PrevStart and @PrevEnd then a.linesellvalue - a.linecostvalue else 0 end) as PreviousGPC
into #temp_table
from Consol..tCustMatRollup a, Consol..tCustomer b, Consol..tSAPMaterials c, IBP_PRD..tProductGroup d
where a.customerno = b.customerno and
c.material = a.material and
d.pg4 = c.pg4 and
d.pg4category = 'workplace'

group by b.sectorcode, d.pg4
order by b.sectorcode,d.pg4 asc

select prodgrp, sectorcode , CurrSales, PreviousSales ,CurrGPC, PreviousGPC,
CASE when PreviousSales = 0 or CurrSales = 0 then 0 else (CurrSales/PreviousSales ) * 100 end as 'Sales%',
CASE when CurrGPC = 0 or PreviousGPC = 0 then 0 else (CurrGPC/PreviousGPC) * 100 end as 'GPC%',
CASE when CurrGPC = 0 or CurrSales = 0 then 0 else ((CurrGPC)*100) / CurrSales as 'CurrGP%',
CASE when CurrGPC = 0 or CurrSales = 0 or PreviousSales = 0 or PreviousGPC = 0 then 0 else (((CurrGPC*100) /CurrSales) / ((PreviousGPC*100)/PreviousSales)) * 100 end as 'YOY'
from #temp_table

union all

select prodgrp, 'Zt', sum(CurrSales), sum(PreviousSales),sum(CurrGPC),sum(PreviousGPC),
CASE when PreviousSales = 0 or CurrSales = 0 then 0 else (sum(CurrSales)/sum(PreviousSales) ) * 100 end as 'Sales%',
CASE when CurrGPC = 0 or PreviousGPC = 0 then 0 else (sum(CurrGPC)/sum(PreviousGPC)) * 100 end as 'GPC%',
CASE when CurrGPC = 0 or CurrSales = 0 then 0 else ((sum(CurrGPC))*100) / sum(CurrSales) end as 'CurrGP%',
CASE when CurrGPC = 0 or CurrSales = 0 or PreviousSales = 0 or PreviousGPC = 0 then 0 else (((sum(CurrGPC)*100)/sum(CurrSales)) / ((sum(PreviousGPC)*100)/sum(PreviousSales))) * 100 end as 'YOY'
from #temp_table
group by prodgrp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-06-12 : 05:16:03

The errors are: Server: Msg 156, Level 15, State 1, Procedure PW_SectorSalesPrdGrp_Summary, Line 22
Incorrect syntax near the keyword 'as'.
Server: Msg 170, Level 15, State 1, Procedure PW_SectorSalesPrdGrp_Summary, Line 23
Line 23: Incorrect syntax near 'CurrGPC'.
Warning: Null value is eliminated by an aggregate or other SET operation.

(180 row(s) affected)

Server: Msg 8134, Level 16, State 1, Procedure PW_SectorSalesPrdGrp_Summary, Line 19
Divide by zero error encountered.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 05:28:35

You are missing an END in line

CASE when CurrGPC = 0 or CurrSales = 0 then 0 else ((CurrGPC)*100) / CurrSales as 'CurrGP%',

The two following errors I think derives from this one.

Change the line above to

CASE when CurrGPC = 0 or CurrSales = 0 then 0 else CurrGPC * 100 / CurrSales END as 'CurrGP%',

Peter Larsson
Helsingborg, Sweden

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-06-12 : 06:17:32

Fixed thanks Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources