ORA-01476除数为零报错 给定Oracle查询异常排查修复求助

报错根因 #

• 错误触发点为SQL中两处sum(rl.idnqty) / sum(rl.expqty)的除法逻辑，当分组后sum(rl.expqty)计算结果为0时，就会触发除零报错。
• 你之前针对max函数、空值处理函数的排查没有命中问题核心，问题根源是聚合后的分母为0的场景。

核心修改点 #

1. SELECT子句中的除法逻辑用NULLIF包裹分母，分母为0时返回空值，不会触发报错：round(sum(rl.idnqty) / NULLIF(sum(rl.expqty),0) * 100, 1)
2. HAVING子句的除法判断直接改为等值判断，彻底规避除法运算，性能更优：原逻辑除法结果为1等价于分子分母相等，直接写sum(rl.idnqty) = sum(rl.expqty)即可，不需要做除法运算。

修复后完整SQL #

select case when count(trlr_num) > 0 then 'Inbounds Need Closed'
             else 'All Good'
        end as "Inbounds To Be Closed"
   from (select t.trlr_num,
                r.invnum "Invoice",
                to_char(nvl(max(rl.init_rcv_dte), sysdate), 'MM/DD/YY HH:MI AM') as "Begin receive date",
                round(sum(rl.idnqty) / NULLIF(sum(rl.expqty),0) *100, 1) as "% Received%"
           from trlr t
           join rcvinv r
             on t.trlr_num = r.trknum
           join rcvlin rl
             on r.trknum = rl.trknum
            and r.supnum = rl.supnum
            and r.invnum = rl.invnum
            and r.wh_id = rl.wh_id
           join rcvtrk rt
             on r.trknum = rt.trknum
            and t.trlr_id = rt.trlr_id
            and r.wh_id = rt.wh_id
          where t.yard_loc_wh_id = 'US_3218'
            and rt.rcvtrk_stat <> 'C'
            and r.invtyp not in ('PKG', 'XFR')
            and (rl.rcvsts <> 'QI' or (rl.rcvsts = 'QI' and t.trlr_seal1 is not null and t.trlr_seal2 is not null))
          group by t.trlr_num,
                r.invnum
         having sum(rl.idnqty) = sum(rl.expqty)
            and nvl(max(rl.init_rcv_dte), sysdate) < sysdate -2)

可选优化 #

如果业务规则要求预期数量expqty为0的记录不需要参与统计，可以直接在WHERE子句新增rl.expqty > 0过滤条件，提前过滤无效数据，减少聚合运算量。

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