Divide by Zero Error

Question

Divide by Zero Error

Toni Chaffin

SSCommitted

Points: 1513
More actions
May 8, 2006 at 1:24 am

#168033

I have a function that compiles a number of different aspects of a Select query and then creates some high-bred fields based on the result. The problem I have is that some of these values contain a zero or Null and therefore I am getting a divide by zero error. The three sums I have are as follows;
Number 1:-
SELECT........, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, BLAH BLAH FROM...
Number 2 :-
SELECT........, fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc, BLAH BLAH FROM..
Number 3 :-
SELECT........, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, BLAH BLAH FROM...
I have been informed that I need to use something like a CASE statement. What I want it to do is that if a ZERO or NULL is detected in any element of the source of the calculation, then I want it to ignore the sum and just place the value of zero in whatever the AS xxxxxxxx dictates.
Could someone point me in a direction here or provide me with a little sample of how to go about doing this in a SELECT statement.
Thanks in advance

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

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

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 1

In addition to my previous post, would I be better to evaluate these conditions using a CASE statment in a second,third and fourth function and then deliver the results to the afore mention function, or could I use a nested statement within the existing SELECT statements?

Regards

JeffB Hall of Fame Points: 3541 More actions · Answer 2

Does this help:

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal

else 0 end AS floorspaceperc,

case when floortotal <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1

else 0 end AS rentrolldiscperc,

case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal

else 0 end AS netrentpersqft, BLAH BLAH

FROM fnWTRalldata...

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 3

Hi JeffB

Thank you for taking the time you did to show this to me. Would I (case when.....) this within the existing Select statement so I could avoid writing other functions?

Kindest Regards

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 4

Hi JeffB

I made a small change to the middle because I was still getting the error, however the change resolved this. Thanks for you help, really bailed me out of a confusing scenario and I actually learned how to do it in the future too.

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal

else 0 end AS floorspaceperc,

case when FinalRtLsincSC <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1

else 0 end AS rentrolldiscperc,

case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal

else 0 end AS netrentpersqft, BLAH BLAH

FROM fnWTRalldata

Regards

Bill Scott SSCrazy Points: 2328 More actions · Answer 5

One of the nicest things about the Case statement is you can use it whereever a scalar value is expected. You can nest them several levels deep. They are really nice when you have to make sure the data is suitable for the manipulation you intend, like your situation looking for nulls and zeros.

case

when somecondition1 then

case

when somecondition2 then X

else Y

end

else Z

end

There is also another form:

case SomeValue

when 1 then A

when 2 then B

else C

end

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 6

Thanks Bill, I appreciate the time you spent in typing that up for me. I only wish books were written in the way that you guys write things on here. Life would be so much easier.

Thanks once again

Regards