sum(t.returnamount)/sum(t.amount)) 除数为0 怎么处理

Question

select   t.id.vendor,  t.id.plant,t.id.rbdate,(sum(t.returnamount)/sum(t.amount)) *1000000 ,sum(t.returnamount),sum(t.amount)from  Srm063 t 

Answer 1

sum(t.amount)是否为0可以通过case when进行判断，当你的除数为0的时候下面的表达式应该是怎样的呢？一个新的计算规则？
(sum(t.returnamount)/sum(t.amount))   *1000000   

Answer 2

select　t.id.vendor,t.id.plant,t.id.rbdate,
case when sum(t.amount) = 0 then 
(sum(t.returnamount)/sum(t.amount))*1000000 else 0 end,
sum(t.returnamount),sum(t.amount) 
from Srm063 t 

Answer 3

可以简单点用decode判断
select t.id.vendor,
       t.id.plant,
       t.id.rbdate,
       (DECODE( sum(t.amount),0,0,sum(t.returnamount) / sum(t.amount))) * 1000000,
       sum(t.returnamount),
       sum(t.amount)
  from Srm063 t

Answer 4

StringBuffer sql=new StringBuffer("select   t.id.vendor,  t.id.plant,t.id.rbdate, case when sum(t.amount) = 0 then  0 else   (sum(t.returnamount)/sum(t.amount)*1000000) end ,sum(t.returnamount) ,sum(t.amount)");