concaténation des status d'un détail sur une ligne

**orafrance** · 01/06/2006, 09h10

Bonjour,

J'essaye désespérément de faire qulequechose qui doit être simple pour qui manipule les datawarehouses mais je ne m'en sors pas.

Exemple :

tab_maitre

id status flag
1 x Y
2 x Y
3 x N

tab_fille
id id_maitre status
1 1 a
2 1 b
3 1 c
4 2 a
5 2 b
6 3 a

je veux mettre à jour la colonne status de tab_maitre où le flag est à 'Y' avec la concaténation des status du fils.

Je voudrais donc :

tab_maitre

id status flag
1 abc Y
2 ab Y
3 x N

j'ai essayé :

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
SELECT id_maitre, rems
  FROM (SELECT   id_maitre, rems, MAX (LENGTH (rems))
            FROM (SELECT e.id_maitre,
                               LEAD (r.status, 1) OVER (ORDER BY r.id_maitre)
                            || ' ' || LEAD (r.status, 2) OVER (ORDER BY r.id_maitre)
                            || ' ' || LEAD (r.status, 3) OVER (ORDER BY r.id_maitre)
                            || ' ' || LEAD (r.status, 4) OVER (ORDER BY r.id_maitre)
                            || ' ' || LEAD (r.status, 5) OVER (ORDER BY r.id_maitre)
                            || ' ' || LEAD (r.status, 6) OVER (ORDER BY r.id_maitre)
                                                                         rems
                      FROM tab_fils e,
                           tab_maitre r
                     WHERE e.status_flag = 'Y'
                       AND e.id_maitre = r.id_maitre
                  ORDER BY 1)
        GROUP BY id_maitre, rems)
;

mais :
- le résultat semble faux
- les perfs sont pas terribles
- je ne connais pas le nombre de fils donc là j'ai été jusqu'à 6 mais rien ne me dit que ça suffit

Bref, voila un problème bien épineux et je vous serais très reconnaissant de me donner un coup de main

**thanaos** · 01/06/2006, 09h30

Bonjour Fred.
Désolé pour la perturbation, mais essayant de comprendre ton code, je m'étonne de l'abscence d'une relation par CONNECT BY PRIOR dans ta requête. Elle n'est pas exploitable ici ?
--------------------------------------------------------------------------
Je suis aveugle. Eclairez moi de votre lumière.

**lalystar** · 01/06/2006, 09h40

Salut Orafrance,

Tu peux essayer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
update  TAB_MAITRE a
set     status = (
          select max(decode(rn, 1, status)) || max(decode(rn, 2, status)) || max(decode(rn, 3, status)) || max(decode(rn, 4, status)) || max(decode(rn, 5, status)) status 
          from         
            (                    
              select  id_maitre, row_number() over(partition  by id_maitre order by id) rn, status 
              from   tab_fille b
            )
          where id_maitre = id 
          group by id_maitre
        )

Ca a l'air de bien marcher, mais on suppose qu'il y aura maximum N status dans la table tab_fille...
Et au niveau du plan, je n'ai pas l'impression que le prédicate filtrant sur l'id du père est "pushé" à l'intérieur de la view online...

Sinon, si tu es en 9i+, tu peux regarder au niveau des fonctions d'aggrégat définie par l'utilisateur.

Laly.

**Médiat** · 01/06/2006, 09h41

Fred regarde là : http://www.developpez.net/forums/sho...d.php?p=311475.

Il y a un exemple sous ORACLE qui correspond à ce que tu veux faire...

**orafrance** · 01/06/2006, 09h57

Envoyé par thanaos

Bonjour Fred.
Désolé pour la perturbation, mais essayant de comprendre ton code, je m'étonne de l'abscence d'une relation par CONNECT BY PRIOR dans ta requête. Elle n'est pas exploitable ici ?

j'y avais bien pensé mais j'arrive pas à m'en sortir

**orafrance** · 01/06/2006, 09h59

Envoyé par Médiat

Fred regarde là : http://www.developpez.net/forums/sho...d.php?p=311475.

Il y a un exemple sous ORACLE qui correspond à ce que tu veux faire...

en effet, c'est exactement ça

je regarde ça de suite

**orafrance** · 01/06/2006, 10h16

pour info, le plan de test est le suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table tab_maitre(id number, remarks varchar2(200), remarks_flag varchar2(1) default 'N');
create table tab_fils(id number, id_maitre number,remarks varchar2(200));
 
insert into tab_maitre values (1,'x','Y');
insert into tab_maitre values (2,'x','Y');
insert into tab_maitre values (3,'x','N');
 
insert into tab_fils values (1,1,'a');
insert into tab_fils values (2,1,'b');
insert into tab_fils values (3,1,'c');
insert into tab_fils values (4,1,'d');
insert into tab_fils values (5,2,'e');
insert into tab_fils values (6,2,'f');
insert into tab_fils values (7,3,'g');

Et voila qui marche presque :

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
SELECT id_maitre, substr(Liste ,2) rems
FROM (SELECT id_maitre, remarks, PRIOR remarks, LEVEL AS niveau, sys_connect_by_path(remarks, ' ') AS Liste 
      FROM tab_fils a 
      where  id_maitre IN (select id_maitre from tab_maitre where remarks_flag = 'Y')
      CONNECT BY id_maitre = PRIOR id_maitre AND remarks > PRIOR remarks 
      START WITH (id_maitre, remarks) IN (SELECT id_maitre, Min(remarks) FROM tab_fils GROUP BY id_maitre) 
     ) b 
WHERE niveau = (SELECT Max(Niveau) 
                FROM (SELECT id_maitre, remarks, PRIOR remarks, LEVEL AS niveau, sys_connect_by_path(remarks, ',') AS Liste 
                      FROM tab_fils a 
      								where  id_maitre IN (select id_maitre from tab_maitre where remarks_flag = 'Y')
                       CONNECT BY id_maitre = PRIOR id_maitre AND remarks > PRIOR remarks 
                      START WITH (id_maitre, remarks) IN (SELECT id_maitre, Min(remarks) FROM tab_fils GROUP BY id_maitre) 
                     ) a 
                WHERE a.id_maitre = b.id_maitre 
               );

ID_MAITRE REMS
---------- -------------------------------
1 a b c d
2 e f
3 g

ça force le respect

le problème c'est sur ID 3 qui devrait donner 'x', je ne devrais même pas le traiter

**orafrance** · 01/06/2006, 10h27

c'est réglé

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
SELECT id_maitre, substr(Liste ,2) rems
FROM (SELECT id_maitre, remarks, PRIOR remarks, LEVEL AS niveau, sys_connect_by_path(remarks, ' ') AS Liste 
      FROM tab_fils a ,tab_maitre tm
      WHERE a.id_maitre = tm.id and tm.remarks_flag = 'Y'     
      where  id_maitre IN (select id_maitre from tab_maitre where remarks_flag = 'Y')
      CONNECT BY id_maitre = PRIOR id_maitre AND remarks > PRIOR remarks 
      START WITH (id_maitre, remarks) IN (SELECT id_maitre, Min(remarks) FROM tab_fils GROUP BY id_maitre) 
     ) b 
WHERE niveau = (SELECT Max(Niveau) 
                FROM (SELECT id_maitre, remarks, PRIOR remarks, LEVEL AS niveau, sys_connect_by_path(remarks, ',') AS Liste 
                      FROM tab_fils a 
      								where  id_maitre IN (select id_maitre from tab_maitre where remarks_flag = 'Y')
                       CONNECT BY id_maitre = PRIOR id_maitre AND remarks > PRIOR remarks 
                      START WITH (id_maitre, remarks) IN (SELECT id_maitre, Min(remarks) FROM tab_fils GROUP BY id_maitre) 
                     ) a 
                WHERE a.id_maitre = b.id_maitre 
               );

**orafrance** · 01/06/2006, 10h29

j'suis passé d'un coût de 3000 à 7 en utilisant IN au lieu de la jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
FROM tab_fils a ,tab_maitre tm
      WHERE a.id_maitre = tm.id and tm.remarks_flag = 'Y'

devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
FROM tab_fils a 
      WHERE a.id_maitre IN (SELECT id FROM tab_maitre tm WHERE tm.remarks_flag = 'Y'

**orafrance** · 01/06/2006, 11h32

Finalement ceci suffit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT id_maitre, liste
  FROM (SELECT id_maitre, liste, niveau,
               MAX (niveau) OVER (PARTITION BY id_maitre) max_niv
          FROM (SELECT     id_maitre, remarks, LEVEL AS niveau,
                           SYS_CONNECT_BY_PATH (remarks, ' ') AS liste
                      FROM tab_fils a
                     WHERE id_maitre IN (SELECT id
                                           FROM tab_maitre
                                          WHERE remarks_flag = 'Y')
                CONNECT BY id_maitre = PRIOR id_maitre
                           AND remarks > PRIOR remarks))
 WHERE niveau = max_niv

**Médiat** · 01/06/2006, 19h56

Fred j'ai utilisé ton idée de la fonction analytique pour simplifier la requête qui se trouve là : http://www.developpez.net/forums/sho...d.php?p=311475.

Merci et

**orafrance** · 02/06/2006, 09h13

par contre, dans mon exemple les perfs sont catastrophiques :'(

je vais voir comment m'en sortir

Sinon, bravo pour l'utilisation du MAX analytique

concaténation des status d'un détail sur une ligne

Oracle

Discussions similaires

Partager

Partager