Divide by Zero Error (1 Viewer)

[Ice Rhino]

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

 

[FoFa]

Here is one to give you an idea:

CASE 
 WHEN fnWTRalldata.floortotal IS NULL THEN 0
 WHEN fnWTRalldata.floortotal 0 THEN 0
 ELSE fnWTRalldata.floortotocc / fnWTRalldata.floortotal 
END AS floorspaceperc

 

[Ice Rhino]

Thank you FoFa

Regards

 

[Ice Rhino]

Here is the ultimate solution I went for.

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 your suggestion, the solution I found was down the road you were heading in.

Thanks