Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Question Unanswered: divide by zero and decode statement

    I am calculating a weighted average number from TABLE (the
    complete select is listed at the bottom).

    The actual weighted average formula is:

    sum((e / d) * (a + b + c)) / sum((a + b + c))

    To avoid divide by zero errors, I use the decode statement
    to check the devisors for NULL and 0. First I check "sum((a + b + c))".
    If <> NULL and <> 0 then I check "sum(d)". If <> NULL and
    <> 0 then I execute the above formula.

    When column d for all records is NULL, the select executes
    fine, returning -3. When column d for all records is 0, the
    select aborts (ORA-01476: divisor is equal to zero), I expected
    to have -4 returned.

    Any ideas or better ways to avoid divide by zero?

    Thanks in advance.


    select decode(sum((a + b + c)),NULL,-1,0,-2, decode(sum(d),NULL,-3,0,-4, sum((e / d) * (a + b + c)) / sum((a + b + c))))
    from TABLE

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I use REPLACE if I might get a zero.
    Can you replace the zero with 1 or will that screw up your formula?
    REPLACE(d,0,1)

    Otherwise you could use a CASE statement to check the value of 'd' before you ran the formula.

    I am also wondering why you use SUM at all.
    Wouldn't you get the same result with:
    PHP Code:
    ((d) * (c)) / (c
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2003
    Posts
    9
    Duck,

    Thanks for the reply. I use the sum because I'm actually grouping
    a lot of records in the table to return the single number. I omitted
    the grouping logic from my post. My bad, I should have explained the
    select a little better. Sorry.

    Can I use a replace with a #, I thought it was just a string
    function (I'm still on 7.3).

    I've got a stored procedure that is simply a select statement that
    tries to summarize a range of records down to a weighted avg. I'm
    trying to do it in just a single select, am I asking to much of sql?

    Thanks again for your input and time. I appreciate it.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •