divided by zero error

Question

Post reply

divided by zero error

jess.

SSC Veteran

Points: 224
More actions
January 2, 2013 at 7:27 am

#268159

Hello,
I am reviving this error when trying to run a query
(48 row(s) affected)
Dec 25 2011 3:30AM
Jan 1 2012 3:30AM
(5 row(s) affected)
Msg 8134, Level 16, State 1, Line 64
Divide by zero error encountered.
This is the code giving me trouble:
select
case
when r1.su is null then r2.su
when r1.su != r2.su then r1.su+'~'+r2.su
else r1.su
end as su,
case
when r1.cur is null then r2.cur
when r1.cur != r2.cur then r1.cur+'~'+r2.cur
else r1.cur
end as cur,r1.dname,r1.rate,r2.dname,r2.rate,round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2) as perc
this is how I tried to fix it:
select
case
when r1.su is null then r2.su
when r1.su != r2.su then r1.su+'~'+r2.su
else r1.su
end as su,
case
when r1.cur is null then r2.cur
when r1.cur != r2.cur then r1.cur+'~'+r2.cur
else r1.cur
end as cur,r1.dname,r1.rate,r2.dname,r2.rate,
case
when (r1.rate+r2.rate) = 0 then 0
else round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2)
end as perc
Any advice as to what I may be doing wrong?
thanks,
.jess

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try changing

case

when (r1.rate+r2.rate) = 0 then 0

else round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2)

end as perc

to

isnull(round(((r1.rate-r2.rate)/(nullif(r1.rate+r2.rate,0)))*100.0,2),0) as perc

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jess. SSC Veteran Points: 224 More actions · Answer 2

I'm still getting the same error when using that.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Can you post some DDL and sample data?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

GSquared SSC Guru Points: 260824 More actions · Answer 4

What datatypes are the rate columns? Float?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jess. SSC Veteran Points: 224 More actions · Answer 5

This has been fixed. Basically, there were a few other divisors in the query that I didn't check as I thought it had something to do with that section alone.

I added case statements to the other divisors and it worked.

Thanks for your help!

jess.