declare startDate varchar2(8):= '20070508 ';
endDate varchar2(8):= '20070808 ';
begin select * from
(select a.rcv_ins_id_cd,nvl(txncntA,0)txncntA,nvl(txncntB,0)txncntB,nvl(txncntC,0)txncntC,nvl(txncntD,0)txncntD,nvl(txncntE,0)txncntE,nvl(txncntF,0)txncntF,nvl(txncntG,0)txncntG,nvl(txncntH,0)txncntH,nvl(txncntI,0)txncntI,nvl(txncntJ,0)txncntJ from
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntA from tsfana where trans_id = 'S25 ' and dom_ext_in = '1 ' and trans_at <=5000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)a
left outer join
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntB from tsfana where trans_id = 'S33 ' and dom_ext_in = '1 ' and trans_at <=5000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)b
on a.rcv_ins_id_cd = b.rcv_ins_id_cd
left outer join
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntC from tsfana where trans_id = 'S25 ' and dom_ext_in = '1 ' and trans_at > 5000 and trans_at <= 10000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)c
on a.rcv_ins_id_cd = c.rcv_ins_id_cd
left outer join
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntD from tsfana where trans_id = 'S33 ' and dom_ext_in = '1 ' and trans_at > 5000 and trans_at <= 10000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)d
on a.rcv_ins_id_cd = d.rcv_ins_id_cd
left outer join
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntE from tsfana where trans_id = 'S25 ' and dom_ext_in = '1 ' and trans_at > 10000 and trans_at <= 50000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)e
on a.rcv_ins_id_cd = e.rcv_ins_id_cd
left outer join
(select rcv_ins_id_cd, nvl(sum(txncnt),0)txncntF from tsfana where trans_id = 'S33 ' and dom_ext_in = '1 ' and trans_at > 10000 and trans_at <= 50000 and settle_dt between startDate and endDate group by rcv_ins_id_cd)f