ORA-01476除数为零报错：PL/SQL查询语句问题求助

问题描述 #

执行以下PL/SQL查询时触发ORA-01476: divisor is equal to zero错误，已添加CASE语句判断除数是否为零，但报错依旧：

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

问题分析 #

外层CASE只判断了聚合后的总和是否为零，但内部除法操作是针对每一行数据执行的。当某一行的ir.avg_price_paid_by_part_prev_year_q4为null或0时，Nvl(ir.avg_price_paid_by_part_prev_year_q4,0)返回0，此时执行ir.avg_price_paid_by_part_q1 / 0会直接触发除数为零错误——这部分计算发生在聚合之前，外层CASE无法提前拦截。

另外，内层的Sum(Case When ... !=0 then 1 Else 0 End)也可能出现总和为0的情况，导致外层除法时再次触发错误。

解决方法 #

需要在每一行的除法操作前就判断除数是否为零，同时处理聚合后的除数为零情况：

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
             OR Sum(Case WHEN Nvl(ir.avg_price_paid_by_part_prev_year_q4,0) != 0 THEN 1 ELSE 0 END) = 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 Nvl(ir.avg_price_paid_by_part_prev_year_q4,0) != 0 
                             AND 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

关键修改点 #

1. 单行除法提前拦截：确保每一行执行除法前，先判断除数是否为零，不为零才进行计算。
2. 新增聚合分母判断：在最外层CASE中，增加对Sum(Case ...)结果是否为零的判断，避免聚合后除法触发错误。
3. 统一条件逻辑：将内层判断除数的逻辑整合，减少重复计算，提升代码可读性。

内容的提问来源于stack exchange，提问作者Jannette