locked
Divide by Zero Error RRS feed

  • Question

  • 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

     

    Monday, May 8, 2006 7:09 AM

Answers

  • 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

     

    Tuesday, May 9, 2006 7:55 AM

All replies

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

    Monday, May 8, 2006 9:29 AM
  • 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

     

    Monday, May 8, 2006 9:32 AM
  • 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)

    Monday, May 8, 2006 3:20 PM
  • 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

     

    Tuesday, May 9, 2006 7:55 AM