how to solve division by zero prob

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

SQL Server Development (2000)

how to solve division by zero prob

Author

Topic

s
Starting Member

5 Posts

Posted - 2003-03-05 : 05:26:59

hi all,

in the following code i have division by zero prob ...that is year 2002 value is 0 so...how to solve that...
declare @year1 int
declare @year2 int
declare @month int

set @year1 = 2002
set @year2 = 2001
set @month =9

select

case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0
else
(
(sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) -
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end)
)* 100 end as Percentage,
b.district_type, b.region_name, b.sun_site,a.map_code

from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site
and a.map_code in ('m027')
and a.oper_month = @month

s

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-03-05 : 06:22:00

What exactly are you trying to acheive. You're code seems to imply that if you're in the current year that you want the percentage to be 0, if this is the case, just move the case to outside the calculation

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-06 : 12:56:52

If you must use this logic, use the SET ARITHABORT ON and SET ARITHIGNORE ON and SET ANSI_WARNINGS OFF to 1. Not abort the query on mathematical errors, 2. Ignore the math errors, and 3. Not give you the error message. However, don't forget to set them back on/off after the query or you may get a mess.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-06 : 13:42:06

OK, Formatting helps a little. First, the outter most CASE I don't think will ever be TRUE, so why have it? a.oper_year may be @year1 or @year2 (or something else), but a.oper_year can not equal both (unless you define them the same, which you dont). So that brings us to the ELSE Condition of the outtermost CASE statement. Again we have the same problem. In this case however, the ONLY tim ethe will work is if a.oper_year = @year2. AND in this case that would roughly translate to 0 - Sum(a.oper_year)/Sum(a.oper_year), or always -1.

What are trying to do (in English).

Thanks

Brett

8-)

Formatted:

SELECT CASE WHEN SUM(
CASE WHEN a.oper_year = @year1 THEN a.amount
ELSE 0
END) =
SUM(
CASE WHEN a.oper_year = @year2 THEN a.amount
ELSE 0
END) THEN 0
ELSE ((SUM(
CASE WHEN a.oper_year = @year1 THEN a.amount
ELSE 0
END) -
SUM(
CASE WHEN a.oper_year = @year2 THEN a.amount
ELSE 0
END)) /
SUM(
CASE WHEN a.oper_year = @year2 THEN a.amount
ELSE 0
END))* 100
END as Percentage

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-06 : 13:45:09

Oh, one more thing. I don't know about anyone else, but If I don't have to mess with settings, I don't. I try to make sure the code is clean and accomodates for any of thos problems.

Also, this is a repost.

Didn't like the answers the first time?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24222

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-07 : 09:20:40

ALSO....I TOTALY missed the boat on the sum aspect of this. I would imagine if you did:

Select @Result1 = Sum(a.amount) From from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site
and a.map_code in ('m027')
and a.oper_month = @month
and a.oper_year = @year1

And

Select @Result2 = Sum(a.amount) From from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site
and a.map_code in ('m027')
and a.oper_month = @month
and a.oper_year = @year2

You would probably make the Engine work less, and simplify your statement.

Hope this helps

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources