执行SQL时 报错 ORA-01476:divisor is equal to zero 的解决方法

这个报错，是因为除数为0，可以将除数转换成非零的整数值

如下面SQL中的红色部分

with tmp as
 (select  paycode, transtate, count(1) cnt_1
    from elmp_trans_orderinfo
   where createtime >= sysdate- 1 / (24 * 12)
     and
   trantype = 1
   group by paycode, transtate)
select channel,case when cnt like '%.%' then to_char(cnt)||'%' else to_char(cnt) end cnt from 
(select 'CMB_SUCCESS' channel, nvl(max(case when paycode = '1308' and transtate = 0 then cnt_1 end), 0) cnt
  from tmp
union all
select 'CMB_TBD', nvl(max(case when paycode = '1308' and transtate = 2 then cnt_1 end), 0) cnt
  from tmp
union all
select 'CMB_FAIL', nvl(max(case when paycode = '1308' and transtate = 1 then cnt_1 end), 0) cnt
  from tmp
union all
select 'CMB_SUCCESS_RATIO', round(nvl(max(case when paycode = '1308' and transtate = 0 then cnt_1 end), 0) * 100 / 
decode((nvl(max(case when paycode = '1308' and transtate = 0 then cnt_1 end), 0) + nvl(max(case when paycode = '1308' and transtate = 2 then 
cnt_1 end), 0) + nvl(max(case when paycode = '1308' and transtate = 1 then cnt_1 end), 0)) ,0,1), 2)
  from tmp
);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-1824320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1824320/