Divide by Zero Error

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 sum, 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

you can use case like this

select case when fnWTRalldata.floortotal = 0 then 0 else fnWTRalldata.floortotocc / fnWTRalldata.floortotal end as floorspaceperc from table

This should help you avoid division by zero errors.

|||

Thanks for your suggestion Andreas.

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

|||

you can also use the iif in reporting services if that helps but it is a pain.

=iif(denominator > 0, numerator/iif(denominator >0, denominator, 1), 0)

|||

This is what I did to solve it. Found the answer elsewhere on the net

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

Thanks for all your suggestions, the solution I found was down the road you were all heading in.

Thanks