Utilisation du PIVOT avec paramètres inconnus

Version imprimable

Bonjour,

Je souhaite réaliser la requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
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 XML (SUM(gad_prime_nette) AS prime FOR (ccp_libelle) IN (SELECT DISTINCT ccp_code FROM   t_cdecpt))                                                   
ORDER BY pol_numpol, sor_ident

sans que mon résultat ne soit du XML. Le problème est que je ne peux pas connaitre à l'avance ni le nombre, ni les libellés de mes ccp_code... Comment faire?

dans sql il est impossible d'avoir un nombre dynamique de colonne.

tu peux tenter du plsql

Code:

1
2
3
4
5
6
7
8
9
10
11
12
var r refcursor
 
declare joblist varchar2(4000);
begin
  select listagg( ''''||job||''' as '||job,',') within group (order by job) 
    into joblist from (select distinct job from emp);
  open :r for 'select * from (select deptno,job from emp) pivot  
    (count(*) for (job) in ('||joblist||'))';
end;
/
 
print r

Code:

1
2
3
4
5
    DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
        30          0          1          1          0          4
        20          2          2          1          0          0
        10          0          1          1          1          0

Merci beaucoup!!! :)

Donc avec ma requête, si j'ai bien compris ça donne ça :

Code:

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

il me dit que j'ai un problème de virgule à la ligne 6 lorsque je l'exécute sous TOAD... ???

Même en ne mettant que ce code la :

Code:

1
2
3
4
5
6
7
8
9
10
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 ccp_code,ccp_libelle from t_cdecpt) pivot  
    (count(*) for (ccp_libelle) in ('||joblist||'))';
end;
/
print r

J'ai toujours un problème de 'virgule' à la ligne 5... J'ai essayé de placer des virgules à plusieurs endroits... Ça ne résout rien

27/07/2011, 11h49
jkofr

Aurai-tu une virgule dans la colonne ccp_libelle ?

Jko
27/07/2011, 12h29
justinedr71

Non non, juste "ccp_libelle"

Hello,

Je parlais du contenu de ta colonne ccp_libelle.

Démonstration:

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||job||''' as '||job,',') within GROUP (ORDER BY job) 
    INTO joblist FROM (SELECT DISTINCT job FROM scott.emp);
  open :r FOR 'select * from (select deptno,job from scott.emp) pivot  
    (count(*) for (job) in ('||joblist||'))';
end;
/
print r
 
DEPTNO                 ANALYST                CLERK                  MANAGER                PRESIDENT              SALESMAN               
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
30                     0                      1                      1                      0                      4                      
20                     2                      2                      1                      0                      0                      
10                     0                      1                      1                      1                      0                      
 
 
select job from scott.emp where job = 'ANALYST';
JOB       
---------
ANALYST
 
update scott.emp set job = 'ANALY,ST' where job = 'ANALYST'
 
select job from scott.emp where job = 'ANALYST';
---------
ANALY,ST
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||job||''' as '||job,',') within GROUP (ORDER BY job) 
    INTO joblist FROM (SELECT DISTINCT job FROM scott.emp);
  open :r FOR 'select * from (select deptno,job from scott.emp) pivot  
    (count(*) for (job) in ('||joblist||'))';
end;
/
print r
 
Error report:
ORA-56901: non-constant expression is not allowed for pivot|unpivot values
ORA-06512: at line 5

jko

27/07/2011, 12h49
justinedr71

J'ai pas mal de possibilités pour le contenu de ma colonne ccp_libelle.

Globalement, j'ai les caractères de [0..9a..zA..Z], mais aussi [.,:-_<>]...

Êtes-vous sûr que le problème vient du contenu de ma chaine? Comment y remédier?

Pour les ',' tu peux rajouter ca

Code:

1
2
3
4
5
6
7
8
9
10
11
12

var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||job||''' as '||replace (job,',',''),',') within GROUP (ORDER BY job) 
    INTO joblist FROM (SELECT DISTINCT  job FROM scott.emp);
  open :r FOR 'select * from (select deptno,job from scott.emp) pivot  
    (count(*) for (job) in ('||joblist||'))';
end;
/
print r

J'ai utilisé votre dernière requête.

Voici ma requête de base :

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
28
29
30
31
 
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, ccp_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, ccp_libelle, 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) AS prime FOR (ccp_libelle) IN ('Vol' AS Vol, 'Incendie' AS Incendie))
order by pol_numpol asc, ver_compteur asc, sor_ident asc

Avec pour exemple les deux libellés Vol et Incendie (qui sont parmis les nombreux libellés existants). A noter que je ne connais pas ni le nombre ni le nom des libellés...

Cette requête fonctionne parfaitement, maintenant, la voici concaténée dans votre requête à vous :

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
28
29
30
31
32
33
34
35
36
37
38
39
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||ccp_libelle||''' as '||replace (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, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_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, ccp_libelle, 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 (ccp_libelle) in ('||joblist||'))';    
end;
/
print r

Ici elle ne fonctionne pas. Peut-être est-ce du aux ' ' de chaque côté de 'GAR' ou encore '03-00' ou même '||joblist||'...

J'obtiend l'erreur suivante :

ORA-06550: Ligne 28, colonne 18 :
PLS-00103: Symbole "GAR" rencontré à la place d'un des symboles suivants :

* & = - + ; < / > at in is mod remainder not rem
<exposant (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between using || multiset member submultiset

Et la?

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
28
29
30
31
32
33
34
35
36
37
38
39
40
 
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||ccp_libelle||''' as '||REPLACE (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, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_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, ccp_libelle, 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 (ccp_libelle) in ('||joblist||'))';    
end;
/
print r

27/07/2011, 16h38
justinedr71

ORA-00917: virgule absente
ORA-06512: à ligne 6

... Il recommence avec cette histoire de virgule...
27/07/2011, 16h40
justinedr71

Tu as bien mis deux quotes (') à la suite, ou bien ce sont des guillemets (")?

Code:

1
2
3
4
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'')))

27/07/2011, 16h48
justinedr71

Ok! C'est bien ce que j'ai...
Pourquoi ce problème de virgule revient...?

Tiens, essaie ca!

En fait le replace est inutile!

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
28
29
30
31
32
33
34
35
36
37
38
39
40
 
 
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, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_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, ccp_libelle, 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 (ccp_libelle) in ('||joblist||'))';    
end;
/
print r

jko

27/07/2011, 16h50
justinedr71

Ah! On change d'erreur :

ORA-00972: l'identificateur est trop long
ORA-06512: à ligne 6

Et il me surligne la ligne :
declare joblist varchar2(4000);

...

Bien,

Voici un exemple pour que tu comprenne
Code:

1 2 3 4 5 6 7 select 1 as MonLibelledecolonnepourtesterlalongeurmax from dual; SQL Error: ORA-00972: identifier is too long 00972. 00000 - "identifier is too long" *Cause: An identifier with more than 30 characters was specified. *Action: Specify at most 30 characters.
En bref, ton libelle de colonne ne peut pas faire plus de 30 caractères.

Soit tu fais un substr (ccp_libelle,1,30), soit tu n'as pas de solution facile...

Bon courage
jko

Super!
Merci beaucoup!!! :)

Voici ma requete finale, qui fonctionne :

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
28
29
30
31
32
33
34
35
36
37
38
39
 
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, ccp_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, ccp_libelle, 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 (ccp_libelle) in ('||joblist||'))';    
end;
/
print r

27/07/2011, 17h19
justinedr71

Ah non une dernière chose. Comment puis-je faire pour que même les garanties ayant un libelle > 30 soient prises en compte.

Ex, j'ai une garantie avec un libelle 'Protection juiridique automobile', 2 caractères de trop!!! Seulement, il me met bien le champ 'Protection juiridique automobi' mais du coup il ne prend pas en compte les valeurs des primes associées à cette garantie dans ma table, ce que je pense normale, car dans ma table ces primes sont associées à 'Protection juiridique automobile' et non 'Protection juiridique automobi'... Comment faire pour palier à cela.

Je pensais aux expressions régulières avec '*' mais je ne sais pas comment le formuler...

Merci pour vos réponses :)

Ceci fonctionne :

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
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

Cependant, si je change de base Oracle, avec pourtant le même schéma, j'ai une erreur avec mon FROM...

27/07/2011, 18h31
jkofr

Hello,

J'ai mis les "" au niveau de l'alias de la colonne.
Il te faut faire le substr au même niveau.

La ou tu le fais, tu impacte le résultat de ta requête.

jko