--计算临分追偿赔款
Select -trunc(nvl(sum(a.ceded_claim), 0), 2) amount,
'P5' tp,
'临分追偿赔款' des,
a.ri_currency_code currency_code
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And b.c_cont_code in ('FT', 'FA')
And B.C_CONT_TYPE <> 'C'
And a.stat_ym = &s_ym
and a.claim_no like 'Chase%'
and a.department_code not like '286%'
Group By ri_currency_code
union all
--计算分入未决赔款
Select trunc(nvl(sum(a.ceded_claim), 0), 2) amount,
'P4'tp,
'分入未决赔款' des,
a.ri_currency_code currency_code
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And a.stat_ym = &s_ym
and a.claim_no not like 'Chase%'
and (a.claim_no like '42000000%' OR a.claim_no like '5TU%')
and a.department_code not like '286%'
Group By ri_currency_code
)
group by currency_code,tp,des;就这句sql,从一张表中计算两个不同的数值,然后分别标记为 '临分追偿赔款' 和 '分入未决赔款',如何才能不通过union all 实现,在线狂等,各位大虾帮帮忙啊!
Select -trunc(nvl(sum(a.ceded_claim), 0), 2) amount,
'P5' tp,
'临分追偿赔款' des,
a.ri_currency_code currency_code
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And b.c_cont_code in ('FT', 'FA')
And B.C_CONT_TYPE <> 'C'
And a.stat_ym = &s_ym
and a.claim_no like 'Chase%'
and a.department_code not like '286%'
Group By ri_currency_code
union all
--计算分入未决赔款
Select trunc(nvl(sum(a.ceded_claim), 0), 2) amount,
'P4'tp,
'分入未决赔款' des,
a.ri_currency_code currency_code
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And a.stat_ym = &s_ym
and a.claim_no not like 'Chase%'
and (a.claim_no like '42000000%' OR a.claim_no like '5TU%')
and a.department_code not like '286%'
Group By ri_currency_code
)
group by currency_code,tp,des;就这句sql,从一张表中计算两个不同的数值,然后分别标记为 '临分追偿赔款' 和 '分入未决赔款',如何才能不通过union all 实现,在线狂等,各位大虾帮帮忙啊!
不同条件的用case when ... else ... end
或sum(case when ... else ... end)
(case when a.claim_no like '42000000%' OR a.claim_no like '5TU%' then 'P4' else 'P5' end) as tp,
(case when a.claim_no like '42000000%' OR a.claim_no like '5TU%' then '分入未决赔款' else '临分追偿赔款' end) as des,
a.ri_currency_code currency_code
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And b.c_cont_code in ('FT', 'FA')
And B.C_CONT_TYPE <> 'C'
And a.stat_ym = &s_ym
and a.claim_no like 'Chase%'
and a.department_code not like '286%'
Group By ri_currency_code
tp,
decode(tp, 'P4', '分入未决赔款', '临分追偿赔款') as des,
trunc(sum(amount), 2)
from (Select a.ri_currency_code currency_code,
NVL(a.ceded_claim, 0) as amount,
case
when a.claim_no not like 'Chase%' and
(a.claim_no like '42000000%' OR a.claim_no like '5TU%') then
'P4'
when b.c_cont_code in ('FT', 'FA') and B.C_CONT_TYPE <> 'C' and
a.claim_no like 'Chase%' then
'P5'
else
'PP'
end as tp
From ceded_detail_pend a, odsdata.v_cont_brief b
Where a.treaty_id = b.n_cont_id
And a.stat_ym = &s_ym
and a.department_code not like '286%')
where tp != 'PP'
group by currency_code, tp