1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| var r refcursor
declare joblist varchar2(4000);
begin
SELECT listagg( ''''||ccp_libelle||''' as '||ccp_libelle,',') within GROUP (ORDER BY ccp_libelle)
INTO joblist FROM (SELECT DISTINCT ccp_libelle FROM t_cdecpt);
open :r FOR 'select * from (select pol_numpol, sor_ident, ccp_libelle, gad_prime_nette
from f_polices, f_sit_objet_risque, f_garantie_dyn, t_cdecpt, f_tarif_ass
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ccp_code
and gad_prime_nette is not null and gad_prime_nette != 0)
pivot (count(*) for (ccp_libelle) in ('||joblist||'))';
end;
/
print r |
Partager