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

  • 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

     

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

  • 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

     

  • 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

  • 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

  • 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

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

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