Bonjour,

Voici ma requête actuelle :

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
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
30
31
32
33
34
35
36
37
38
39
40
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle) 
    INTO joblist FROM (SELECT DISTINCT substr(ccp_libelle,1,30) as libelle FROM t_cdecpt);
 
      open :r FOR 'SELECT *
FROM (select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, substr(ccp_libelle,1,30) as libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_ident = mvt_ptrsuivantid
and mvt_ptrverid = ver_ident
and pol_ptrpasid = pas_ident
and pas_ident = pri_ptrpasident
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+)
and pol_ptrinid = in_ident (+)
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ta_code
and ta_cle = tga_cletarif
and tga_code_compta = ccp_code
and gad_code = tga_code
and tga_genre = ''GAR''
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval(''03-00'')))
and ((sor_datedebut != sor_datefin) or (sor_datefin is null and sor_datedebut != pol_datresil) 
or (sor_datefin is null and pol_datresil is null and sor_datedebut != pol_datechpro))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, substr(ccp_libelle,1,30), pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type ) 
pivot (sum(gad_prime_nette) for (libelle) in ('||joblist||'))
order by pol_numpol asc, ver_compteur asc, sor_ident asc';    
end;
/
print r

Je veux pouvoir l'exécuter sur une base Oracle 10G... Or, PIVOT et LISTAGG n'existent pas...
De plus, je ne connais pas à l'avance ni le nombre, ni les valeurs de mes ccp_libelle... Il varie suivant les bases clients sur lesquelles tournera ma requête...

Comment faire???

Merci pour votre aide