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