insert into db2inst1.mid_custowe_filter_201002
select id_no,
sum(should_pay),
sum(favour_fee),
sum(should_pay-favour_fee),
sum(case when substr(fee_code,1,2)='0a' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0c' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0d' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0e' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0f' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0g' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0h' then should_pay-favour_fee else 0 end),
......
sum(case when fee_code='0t2m' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0t2n' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0u2o' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0u2p' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0w2q' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0w2r' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0y3f' then should_pay-favour_fee else 0 end),
sum(case when fee_code='0y3g' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1d2i' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1d2j' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1j2u' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1j2v' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1k2w' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1k2x' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1m2y' then should_pay-favour_fee else 0 end),
sum(case when fee_code='1m2z' then should_pay-favour_fee else 0 end),
sum(case when substr(fee_code,1,2)='0b' then should_pay-favour_fee else 0 end)
from ODS_ACCT_SHOULDITEM_201002
group by id_no
[Wed Mar 10 11:18:16 BEIST 2010] -> ERROR:e database manager is not able to accept new requests, has terminated all requests in progress, or has terminated your particular request
due to an error or a force interrupt. SQLSTATE=55032
LINE=0101
db2 "select count(*) from ODS_ACCT_SHOULDITEM_201002"
1
-----------
404700152
---------------------------------------
觉得还是把sum,case when分开做可行性较高。谢谢大家
[
本帖最后由 scott_star 于 2010-3-10 18:06 编辑 ]
收起