IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Equivalent de PIVOT et LISTAGG en 10g


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Profil pro
    Inscrit en
    Juin 2010
    Messages
    111
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juin 2010
    Messages : 111
    Par défaut Equivalent de PIVOT et LISTAGG en 10g
    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

  2. #2
    Membre confirmé
    Profil pro
    Inscrit en
    Mars 2007
    Messages
    90
    Détails du profil
    Informations personnelles :
    Âge : 52
    Localisation : Belgique

    Informations forums :
    Inscription : Mars 2007
    Messages : 90
    Par défaut
    si je comprends bien ton problème, tu devrais trouver une réponse ici

  3. #3
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 953
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 953
    Par défaut
    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 : 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
    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 : 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
    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 : 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
    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 : 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
    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.

Discussions similaires

  1. un pivot 'dynamique' pour oracle 10g
    Par erigoal dans le forum PL/SQL
    Réponses: 14
    Dernier message: 03/06/2009, 21h45
  2. Aide pivot oracle 10g
    Par erigoal dans le forum SQL
    Réponses: 2
    Dernier message: 01/06/2009, 16h36
  3. Equivalence found_rows Oracle 10g
    Par shadeoner dans le forum SQL
    Réponses: 13
    Dernier message: 25/09/2008, 14h20
  4. [Kylix] Equivalent ShellExec en CLX
    Par Anonymous dans le forum EDI
    Réponses: 7
    Dernier message: 14/08/2002, 11h55
  5. [Kylix] equivalent winsock avec kylix
    Par Victor dans le forum EDI
    Réponses: 2
    Dernier message: 08/05/2002, 07h43

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo