Divide by Zero

Question

Divide by Zero

pommguest99

SSC Veteran

Points: 214
More actions
April 4, 2008 at 11:08 am

#180883

When I run the following statement, I get 'Divide by zero error encountered'. I dont have any control over these column values & so, I have to circumvent this & how I do this ?
DurationPercent =
case
when ea.CommSale > b.TEAMSALESCOMMAMT
then (1-b.TEAMSALESCOMMAMT/ea.CommSale )
when b.TEAMSALESCOMMAMT > ea.CommSale
then (1-ea.CommSale /b.TEAMSALESCOMMAMT)
when b.TEAMSALESCOMMAMT = 0 then 1
when ea.CommSale = 0 then 1
when b.TEAMSALESCOMMAMT is null then 1
when ea.CommSale is null then 1
else 1
end
Thanks a bunch !

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Move the tests for 0 first in the Case statement. That should get it.

- 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

GSquared SSC Guru Points: 260824 More actions · Answer 2

I just took another look at your code, and, regardless of the effect of zeroes on it, are you sure you want to divide two different directions in the same code?

For example, if you look at this column of data, and it says, "50%", does that mean that TEAMSALESCOMMAMT is half of CommSale, or that CommSale is half of TEAMSALESCOMMAMT? It could be either way. In most cases (maybe not yours, but that would be a very rare exception), these ratios would be handled very differently from each other.

- 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

pommguest99 SSC Veteran Points: 214 More actions · Answer 3

Gsquared,

That worked & Thanks a bunch !

I guess one cant be careful enough !

pommguest99 SSC Veteran Points: 214 More actions · Answer 4

Im not sure either but they are running some kind of statistical analysis on these ... logistical regression analysis..

GSquared SSC Guru Points: 260824 More actions · Answer 5

Okay. If you're in a position to do so, you might just want to double-check that point with the people who are going to be using it.

- 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