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

  1. #1
    Membre du Club
    Oracle10 : concatener champs texte de plusieurs lignes
    bonjour,

    Soit les données suivantes:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    blocNote     ma_date          affaire
    aa              01/02/2007      144000
    bb              01/03/2007      144000
    cc              19/11/2007      144000

    Résultat attendu:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    blocNote     ma_date          affaire
    aa bb cc     19/11/2007      144000



    Je souhaite obtenir 1 seule ligne en prenant le max de la date et en concaténant tous mes BlocNotes (avec un espace entre chaque....).

    Pour la date, je pense pouvoir utiliser :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    max(MA_DATE) over(partition by affaire)


    Mais comment faire pour concatener les valeurs de blocnote de chaque ligne ?


    Si quelqu'un a une idée, ce serait cool !

    Merci d'avance !

  2. #2
    Membre averti
    A mon avis, il faut que passes par du PlSql et utilser un curseur

    LBO72

  3. #3
    Membre du Club
    ok...

    domage que cela ne puisse pas se faire dans un select avec une fonction analytique...

    Merci quand même.

    Je reste ouverte à d'autres idées.

  4. #4
    Rédacteur

    Salut,

    Essaie ce code, mais il faut changer le nom de la table
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    SELECT     SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
          FROM 
        (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
                       MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
                       ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq
                  FROM note_tab
                 WHERE blocnote IS NOT NULL)                             
    WHERE seq = cnt
    START WITH seq = 1
    CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;

  5. #5
    Expert éminent sénior
    Non ça ne marche pas
    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
    28
    29
    30
    31
    32
    33
    34
     
    create table t_p
    (
      blocNote     varchar2(2),
      ma_date      date,
      affaire      Number(6)
    )
    /
     
    insert into t_p values('aa','01/02/2007',144000);
    insert into t_p values('bb','01/03/2007',144000);
    insert into t_p values('cc','19/11/2007',144000);
    insert into t_p values('cc','19/11/2007',144001);
    insert into t_p values('dd','19/11/2007',144001);
    insert into t_p values('ee','10/12/2007',144001);
    /
    SELECT SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
          FROM (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
                       MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
                       ROWNUM seq
                  FROM t_p
                 WHERE blocnote IS NOT NULL)
         WHERE seq = cnt
    START WITH seq = 1
    CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire
    /
     
    NAME_LIST
    -----------------------------------------
     
    MA_DATE
    --------
    aa bb cc
    19/11/07


    Par contre cette solution marche si tu connais d'avance le max(blocnote) group by affaire. Sinon, des autres solutions sont envisageable (y compris celles similaire à la proposition de salim11, valable à partir d'Oracle 10 seulement)
    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
     
    Select affaire, max(ma_date),
           max(decode(seq,1,blocnote,null)),
           max(decode(seq,2,blocnote,null)),
           max(decode(seq,3,blocnote,null))
    from (
      select affaire, ma_date, blocnote, row_number() over (partition by affaire order by ma_date) seq
      from t_p)
    where seq <= 3
    group by affaire
    /
       AFFAIRE MAX(MA_D MA MA MA
    ---------- -------- -- -- --
        144000 19/11/07 aa bb cc
        144001 10/12/07 cc dd ee

  6. #6
    Rédacteur

    Salut,

    Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code .

    Voici la démonstration que ça marche bien
    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   affaire,  SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
      2         FROM
      3       (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
      4                      MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
      5                      ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq
      6                 FROM t_p
      7                WHERE blocnote IS NOT NULL)
      8   WHERE seq = cnt
      9   START WITH seq = 1
     10   CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;
     
            AFFAIRE NAME_LIST                               MA_DATE
    -------------- ---------------------------------------- ------------
            144000 aa bb cc                                 19/11/2007
            144001 cc dd ee                                 10/12/2007
     
    SQL>

  7. #7
    Expert éminent sénior
    avec des perfs probablement plus prometteuse en plus

  8. #8
    Expert éminent sénior
    Citation Envoyé par salim11 Voir le message
    Salut,

    Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code .
    ...
    Bonjour M Salim 11,

    Si vous relisez ce que j’ai écrit vous pouvez noter que je n’ai ne pas douté de la faisabilité de votre solution J’ai simplement remarqué que le code posté ne donne pas le bon résultat et que votre solution est valable à partir d’Oracle 10 seulement.
    Par contre pourriez-vous m’expliquer que est-ce que vous voulez dire par « j'ai corrigé ma requête même avant que tu post ton code » ?

  9. #9
    Membre du Club
    Merci à tous !!

    Je vois que les challenges techniques ont toujours des preneurs !
    Heureusement qu'il y a des pointures comme vous !
    Merci mille fois, car ce n'était absolument pas évident pour une débutante+ comme moi .

  10. #10
    Expert confirmé
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    create table t as (
    select 'aa' blocnote,to_date('01/02/2007','DD/MM/YYYY') ma_date,44000 affaire from dual union all
    select 'bb',to_date('01/03/2007','DD/MM/YYYY'),44000 from dual union all
    select 'cc',to_date('19/11/2007','DD/MM/YYYY'),44000 from dual )
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select
      wmsys.wm_concat(blocnote) blocnote,
      max(ma_date) ma_date,
      affaire
    from t
    group by affaire;

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    BLOCNOTE MA_DATE                AFFAIRE
    -------- ------------------- ----------
    aa,bb,cc 2007-11-19_00:00:00      44000

  11. #11
    Expert éminent sénior
    la requête qui calme tout le monde

  12. #12
    Membre du Club
    Bonjour,
    c'est quoi :

  13. #13
    Expert éminent sénior
    c'est dispo depuis quelle version?
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  14. #14
    Membre averti
    Trés trés fort Laurent .....
    Toute mon admiration

    LBO72.

  15. #15
    Expert confirmé
    Citation Envoyé par isn44 Voir le message
    Bonjour,
    c'est quoi :
    c'est une fonction d'aggrégat. Je l'ai découvert par hasard en cherchant :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    select owner,object_name from dba_procedures where aggregate='YES';


    Je ne peux pas dire dans quelle version c'est apparu, ce n'est pas documenté et les user-defined-aggregates (UDAG) sont apparues en 9iR1. Si quelqu'un a une 9iR1 pour confirmer, merci

  16. #16
    Expert éminent sénior
    Merci Laurent, cella je ne le connaissais pas.
    Par contre il faut avoir installé Oracle Workspace Manager d'abord

  17. #17
    Expert confirmé
    Citation Envoyé par mnitu Voir le message
    Merci Laurent, cella je ne le connaissais pas.
    Par contre il faut avoir installé Oracle Workspace Manager d'abord
    c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.

  18. #18
    Rédacteur

    Salut,

    Merci Laurent, Tu nous proposes toujours de solutions extraordinaires.

    J’apprends toujours de toi de nouvelles techniques.

    Salim.

  19. #19
    Expert éminent sénior
    Citation Envoyé par laurentschneider Voir le message
    c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.
    Mon DBA n'utilise pas DBCA
    Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?

  20. #20
    Expert confirmé
    Citation Envoyé par mnitu Voir le message
    Mon DBA n'utilise pas DBCA
    Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?
    bon tu peux installer wm manuellement avec @?/rdbms/admin/owminst.plb

    j'imagine que la fonction est utilisable en prod, pour être sûr tu peux ouvrir un ticket sur metalink... mais ça a l'air de fonctionner. Tu peux aussi développer ta propre fonction comme asktom/stragg