PL/SQL divide by zero

Question

I am having an issue when trying to run this statement with the error ORA-01476: divisor is equal to zero. I checked the values that are being used to divide by and put in a case statement to check that if it is zero then do not continue, but I am still getting the error.

 SELECT ir.contract as Site, ir.purchase_group,
 ir.purchase_group_description, ir.current_quarter,
 Sum(Nvl(ir.avg_price_paid_by_part_prev_year_q4,0)) test,
 Sum(Nvl(ir.avg_price_paid_by_part_q1,0)) test1   , Case When (
 Sum(Nvl(ir.avg_price_paid_by_part_prev_year_q4,0)) = 0 or
 Sum(Nvl(ir.avg_price_paid_by_part_q1,0)) = 0 ) Then 0 Else   
 Round(Sum(     case when Nvl(ir.avg_price_paid_by_part_prev_year_q4,0)
 = 0 then NULL else 
                    case when  (100-( ir.avg_price_paid_by_part_q1 / Nvl(ir.avg_price_paid_by_part_prev_year_q4,0) )*100) = 100 then 0 Else
                round(((100-( ir.avg_price_paid_by_part_q1 / Nvl(ir.avg_price_paid_by_part_prev_year_q4,0) )*100) *-1),4) end end )
 
                
                / Sum(Case When round(((100-( ir.avg_price_paid_by_part_q1 /
 Nvl(ir.avg_price_paid_by_part_prev_year_q4,0) )*100) *-1),4) != 0 then
 1 Else 0 End ),4)
         End       avg_price_paid_by_part_q1_change_perc --,
        
      
     FROM ifsinfo.inflation_report_qr ir   where ir.contract  = 'NOPG'   Group by ir.contract, ir.purchase_group,
 ir.purchase_group_description, ir.current_quarter

Can anyone help please. Thanks in advance.

Answer 1

There are 4 dividing operations in that query.

You're explicitly saying: if column value doesn't exist (i.e. it is NULL, replace it by zero 0), e.g.

/ NVL (ir.avg_price_paid_by_part_prev_year_q4, 0)
       --------------------------------------  -
       if this is NULL, then divide by ......  zero

Do you really want to do that? If not, what do you expect as a result?

Maybe you'd be happy to divide by a very large number and get a close-to-zero result. This is what you have now:

SQL> select 125 / nvl(&par_number, 0) result from dual;
Enter value for par_number: null
select 125 / nvl(null, 0) result from dual
           *
ERROR at line 1:
ORA-01476: divisor is equal to zero

This is a very small number:

SQL> select 125 / nvl(&par_number, 1E99) result from dual;
Enter value for par_number: null

    RESULT
----------
1,2500E-97

SQL>

Or, maybe you don't want to get any result at all; in that case, remove all NVLs:

SQL> select 125 / &par_number result from dual;
Enter value for par_number: null

    RESULT
----------
                 <-- this is a NULL

---

Basically, it depends on what you want to get. Just don't divide by zero, that's not allowed.

Answer 2

The simplest way to avoid this error is to find every divisor (right-hand side of a division operator) and if there is any possibility of it evaluating to 0, wrap it in a NULLIF function. You can safely feed a NULL into division and the result is NULL rather than error. This is usually desirable.

So for example:

    ir.avg_price_paid_by_part_q1 / NULLIF(ir.avg_price_paid_by_part_prev_year_q4,0)

. . .

  . . . / NULLIF(Sum(...),0)

etc..