Equivalent de PIVOT et LISTAGG en 10g

[justinedr71]

Bonjour,

Voici ma requête actuelle :

Code SQL :

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

[rafuoner]

si je comprends bien ton problème, tu devrais trouver une réponse ici

[skuatamad]

Effectivement le lien proposé par rafuoner permettra de gérer la conversion du listagg vers des solutions plus compatible mais comme il y a aussi la problématique du pivot ça n'est pas suffisant.
Par contre ton fonctionnel est lourd et complexe, je te propose donc une approche autour du schema scott plus facile à développer pour moi et testable pour tout le monde, mais je pense que ça t'aidera à aborder ta problématique.

Partons de la requête suivante qui aggrège la somme des salaires par departement et par job :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> SELECT d.dname,e.job, sum(e.sal) AS sum_sal
  2    FROM scott.dept d
  3    JOIN scott.emp e ON e.deptno = d.deptno
  4   GROUP BY d.dname,e.job;
 
DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES          MANAGER         2850
SALES          CLERK            950
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING     CLERK           1300
RESEARCH       MANAGER         2975
SALES          SALESMAN        5600
RESEARCH       ANALYST         6000
RESEARCH       CLERK           1900
 
9 rows selected.

L'idée est donc de pivoter la somme autour des jobs pour les dept.
En reprenant la technique à base de listagg et de pivot au sein d'un curseur dynamique, on obtient :

Code :

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
SQL> var r refcursor
SQL> declare
  2   joblist varchar2(4000);
  3  begin
  4    SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle)
  5      INTO joblist FROM (SELECT DISTINCT job AS libelle FROM emp);
  6  
  7    open :r FOR
  8    'select *
  9       from (
 10     select d.dname, e.job, e.sal
 11       from scott.dept d
 12       join scott.emp e on e.deptno = d.deptno
 13            )
 14      pivot (sum(sal) for (job) in ('||joblist||'))';
 15  end;
 16  /
 
PL/SQL procedure successfully completed.
 
SQL> print r
 
DNAME             ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING                      1300       2450       5000
RESEARCH             6000       1900       2975
SALES                            950       2850                  5600

Par contre PIVOT est spécifique aux versions 11G.
Ci-dessous une version statique de la requête pour pivoter les lignes fonctionnant sur les versions antérieures à la 11G :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT d.dname,
  2         sum(case when e.job ='ANALYST'   then e.sal end) AS ANALYST,
  3         sum(case when e.job ='CLERK'     then e.sal end) AS CLERK,
  4         sum(case when e.job ='MANAGER'   then e.sal end) AS MANAGER,
  5         sum(case when e.job ='PRESIDENT' then e.sal end) AS PRESIDENT,
  6         sum(case when e.job ='SALESMAN'  then e.sal end) AS SALESMAN
  7    FROM scott.dept d
  8    JOIN scott.emp e ON e.deptno = d.deptno
  9   GROUP BY d.dname
 10  /
 
DNAME             ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING                      1300       2450       5000
RESEARCH             6000       1900       2975
SALES                            950       2850                  5600

Il faut donc rendre cette requête dynamique pour gérer un nombre de job aléatoire.
Pour ça on construit la requête dynamiquement de façon plus explicite que la requête dynamique à base de pivot :

Code :

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
SQL> var r refcursor
SQL> declare
  2   l_query varchar2(4000);
  3  begin
  4    l_query := l_query || 'select d.dname '||chr(10);
  5    FOR c IN (SELECT DISTINCT job AS libelle FROM emp ORDER BY job) loop
  6      l_query := l_query || '     ,sum(case when e.job = '''||c.libelle||''' then e.sal end) as "'||c.libelle||'" '||chr(10);
  7    end loop;
  8    l_query := l_query || '  from scott.dept d '||chr(10);
  9    l_query := l_query || '  join scott.emp e on e.deptno = d.deptno '||chr(10);
 10    l_query := l_query || ' group by d.dname';
 11    --dbms_output.put_line(l_query);
 12    open :r FOR l_query;
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
SQL> print r
 
DNAME             ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING                      1300       2450       5000
RESEARCH             6000       1900       2975
SALES                            950       2850                  5600

Evidemment on est loin d'une réponse toute faite, et il y a beaucoup de travail pour rendre l'approche compatible avec le fonctionnel existant.
Un conseil commence par écrire une requête qui pivotera statiquement (avec quelques colonnes) ton besoin avant de commencer la procédure dynamique.

Bon courrage pour l'implémentation !

PS : Sinon il y a aussi la version plus générale du pivot dynamique développé par thomas kyte, mais c'est peut être une complexité inutile dans ton cas.