1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| select B.classe, count (*) nombre from
(select a.classs classe,
rpad(a.INSUREDD||' '||f.namee,50) Assuré,
nvl(sum(decode(doc_typee,13,nvl(amountt,0))),0) Montant_Reg
from cl_gen a,cl_td C,broker e,insured f
where a.cl_numm=c.cl_numm(+)
and a.brokerr(+) = e.brokerr
and a.insuredd=f.insuredd
and nvl(decode(doc_typee,12,decode(postingg,1,amountt,2,amountt*-1),13,amountt*-1),0) !=0
and c.document_datee(+) between '01/01/2012' and '31/12/2012'
group by a.INSUREDD||' '||f.namee,bro_sub_typee, a.classs)B
group by B.classe
union
SELECT distinct A.classe,A.Montant_sp
FROM
(select
distinct a.classs classe,
nvl(sum(decode(doc_typee,13,decode(postingg,2,amountt,1,amountt*-1))),0) Montant_sp
from cl_gen a,cl_td c,broker e
where a.cl_numm =c.cl_numm(+)
and a.brokerr(+) = e.brokerr
and a.bro_ins in (1,2)
and c.document_datee(+) between '01/01/2012' and '31/12/2012'
group by
a.classs
having nvl(sum(decode(doc_typee,13,decode(postingg,2,amountt,1,amountt*-1))),0) <> 0
) A
WHERE A.Montant_sp <>0
GROUP BY A.classe,A.Montant_sp |
Partager