Problème requête hiérarchique

Version imprimable

Bonjour,

j'ai une table "service" construite de cette façon :
Id, Id_parent, nom, niveau
1,0, 'direction', 0
2,1, 'ss direction 1', 1
etc...

j'essaie de faire une requete pour obtenir ceci :

id, niv_1, niv_2, niv_3 etc....
1, direction, null, null
2, direction, ss direction, null
etc...

voici la requete qui devrait normalement fonctionner mais je n'ai pas le résultat ci dessus...
Code:

1 2 3 4 5 6 7 8 9 10 select id, niv0.nom, niv1.nom, niv2.nom from ( select * from service where niveau=0) niv0 left outer join (select * from service where niveau=1) niv1 on (niv0.id=niv1.id_parent) left outer join (select * from service where niveau =2) niv2 on (niv1.id=niv2.id_parent)
et ceci pour autant de niveau de la table service...

mais je n'ai pas le résultat souhaité...

Merci pour votre aide.

Bonjour,
Les "SELECT * FROM service" ne servent à rien dans le FROM, il suffit de prendre la table service avec un alias différent à chaque fois.
En inversant le sens des jointures externes on tombe sur un résultat plus proche de celui attendu :
Code:

1 2 3 4 5 6 7 SELECT niv0.id, niv0.nom, niv1.nom, niv2.nom FROM service niv0 LEFT OUTER JOIN service niv1 ON (niv1.id=niv0.id_parent) LEFT OUTER JOIN service niv2 ON (niv2.id=niv1.id_parent)

17/03/2011, 07h41
Go_Ahead

Bonjour,

Tedo01, merci pour ta réponse. Au départ j'ai utilisé ce type de script (que j'ai trouvé sur le net), mais il ne me retourne pas ce que je veux...
Exemple, ton script me retournera ceci :

id, niv0.nom, niv1.nom, niv2.nom
1, direction, null, null,
1, direction, ss dir1, null
1, direction, ss dir1, ss dir2

ce que je voudrais c'est :
1, direction, null, null,
2, direction, ss dir1, null
3, direction, ss dir1, ss dir2

J'ai mis à jour mon script, j'y intègre le niveau. J'essaie de "morceler" le script car il ne me retourne pas l'id souhaité...

Merci

Bonjour,
Le problème, c'est que dans le résultat attendu l'id ne correspond pas toujours au même niveau.
Ce que je ferais pour représenter tous les niveaux possibles, c'est l'union de requêtes, chacune sur un niveau :

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
WITH service AS
(
      SELECT 1 id, null id_parent, 'direction' nom, 1 niveau FROM dual
UNION SELECT 2,    1,              'ss-dir1'      , 2        FROM dual
UNION SELECT 3,    1,              'ss-dir2'      , 2        FROM dual
UNION SELECT 4,    2,              'ss-dir3'      , 3        FROM dual
UNION SELECT 5,    2,              'ss-dir4'      , 3        FROM dual
 
)
SELECT niv3.id, 
       niv3.niveau,
       niv1.nom, 
       niv2.nom, 
       niv3.nom
           FROM service niv3 
LEFT OUTER JOIN service niv2 ON (niv2.id=niv3.id_parent)
LEFT OUTER JOIN service niv1 ON (niv1.id=niv2.id_parent)
WHERE niv3.niveau = 3
UNION
SELECT niv2.id, 
       niv2.niveau,
       niv1.nom, 
       niv2.nom, 
       NULL
           FROM service niv2 
LEFT OUTER JOIN service niv1 ON (niv1.id=niv2.id_parent)
WHERE niv2.niveau = 2
UNION
SELECT niv1.id, 
       niv1.niveau,
       niv1.nom, 
       NULL, 
       NULL
FROM service niv1 
WHERE niv1.niveau = 1

Utilisez sys_connect_by_path

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> WITH service AS
  2  (
  3        SELECT 1 id, NULL id_parent, 'direction' nom, 1 niveau FROM dual
  4  UNION SELECT 2,    1,              'ss-dir1'      , 2        FROM dual
  5  UNION SELECT 3,    1,              'ss-dir2'      , 2        FROM dual
  6  UNION SELECT 4,    2,              'ss-dir3'      , 3        FROM dual
  7  UNION SELECT 5,    2,              'ss-dir4'      , 3        FROM dual
  8  )
  9  Select id, nom, sys_connect_by_path(nom,'/') As text
 10    from service
 11   connect by prior id = id_parent
 12   start with id_parent Is Null
 13  /
 
        ID NOM       TEXT
---------- --------- --------------------------------------------------------------------------------
         1 direction /direction
         2 ss-dir1   /direction/ss-dir1
         4 ss-dir3   /direction/ss-dir1/ss-dir3
         5 ss-dir4   /direction/ss-dir1/ss-dir4
         3 ss-dir2   /direction/ss-dir2
 
SQL>

18/03/2011, 07h45
Go_Ahead

Bonjour à tous,

Merci pour vos réponse, mais cela ne fonctionne pas...
par exemple pour le niveau 1=> j'ai un mix du niveau 2 et niveau 1... alors que je prends bien en compte le nombre de niveau lorsque je fais mon self join...
De plus, le

Code:

WITH service AS

ne fonctionne pas chez moi (sous sqldeveloper)...

Merci pour votre aide!
18/03/2011, 10h00
tedo01

Bonjour,
Je ne comprends ton problème.
1. J'ai testé ma requête sous SQL Developer et elle fonctionne. Le WITH est connu d'Oracle.
2. En mettant les données de ton premier post, j'obtiens le résultat que tu demandes...
Est-ce que tu peux nous donner un jeu d'essai un peu plus étoffé du contenu de la table service accompagné du résultat attendu ?
18/03/2011, 10h34
Waldar

Citation:

Envoyé par tedo01

Le WITH est connu d'Oracle.

À partir de la 9i !
18/03/2011, 11h44
tedo01

C'est juste :oops:
Comme go_ahead mentionnait le client SQL Developer, je n'ai pas pensé à la version d'Oracle.
18/03/2011, 11h52
Go_Ahead

Je suis en 10g ...
il me met cette erreur lorsque j'essaie d'executer un

Code:

with matable as ...

Citation:

ORA-00905
18/03/2011, 12h12
skuatamad

C'est la version du client qui compte (à moins que tu ais mal copier/coller) donc si client 8i sur une 10g WITH ne fonctionne pas.

De toute façon dans les solutions proposées avec WITH, WITH n'est là que pour simuler ta table, tu n'en as pas besoin pour executer la requête sur ton environnement.
18/03/2011, 13h28
Go_Ahead

1 pièce(s) jointe(s)

voici un jeu de ma table...(p.j.)
je suspecte que les niveaux ne reflètent pas la réalité...

Re-bonjour,
Est-ce que le résultat de cette requête correspond au résultat attendu (tu ne l'as pas donné) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    SELECT N1.id, 
           N1.nom,
           NVL(N4.nom, NVL(N3.nom, NVL(N2.nom, N1.nom))) Niv4,       
           (CASE WHEN N4.nom IS NOT NULL THEN N3.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NOT NULL THEN N2.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NULL AND N2.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv3,       
           (CASE WHEN N4.nom IS NOT NULL THEN N2.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv2,       
           (CASE WHEN N4.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv1
FROM service N1
LEFT JOIN service   N2 ON N1.id_parent=N2.ID
LEFT JOIN service   N3 ON N2.id_parent=N3.ID
LEFT JOIN service   N4 ON N3.id_parent=N4.ID

19/03/2011, 13h03
Go_Ahead

Bonjour,

@tedo01 :
je vais essayer ce script et vous dirais si cela fonctionne.
Moi j'attends un résultat de ce type
niveau_0 , niveau_1, niveau_2, niveau_3, id_service
direction , null , null , null , 1
direction , ss dir , null , null , 2
direction , ss dir , ss dir 2 , null , 3
direction , ss dir , ss dir 2 , div , 4
...

merci pour votre aide

Citation:

Envoyé par tedo01

Re-bonjour,
Est-ce que le résultat de cette requête correspond au résultat attendu (tu ne l'as pas donné) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    SELECT N1.id, 
           N1.nom,
           NVL(N4.nom, NVL(N3.nom, NVL(N2.nom, N1.nom))) Niv4,       
           (CASE WHEN N4.nom IS NOT NULL THEN N3.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NOT NULL THEN N2.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NULL AND N2.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv3,       
           (CASE WHEN N4.nom IS NOT NULL THEN N2.nom
                 WHEN N4.nom IS NULL AND N3.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv2,       
           (CASE WHEN N4.nom IS NOT NULL THEN N1.nom
                 ELSE NULL           
            END)    Niv1
FROM service N1
LEFT JOIN service   N2 ON N1.id_parent=N2.ID
LEFT JOIN service   N3 ON N2.id_parent=N3.ID
LEFT JOIN service   N4 ON N3.id_parent=N4.ID

Bonjour,

J'ai pu essayé cette requete mais cela ne produit pas ce que je souhaite...

merci pour votre aide

23/03/2011, 09h27
tedo01

Bonjour,
OK, mais est-ce que tu peux nous donner le résultat attendu avec le jeu d'essai que tu nous a envoyé ? Sinon, on ne va pas avancer...

Avec cette table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop table service;
 
create table service AS
(
      SELECT  1 id, NULL id_parent, 'direction' nom, 1 niveau FROM dual
UNION SELECT  2,    1,              's-dir1'       , 2        FROM dual
UNION SELECT  3,    1,              's-dir2'       , 2        FROM dual
UNION SELECT  4,    2,              'ss-dir1'      , 3        FROM dual
UNION SELECT  5,    2,              'ss-dir2'      , 3        FROM dual
UNION SELECT  6,    4,              'div1'         , 4        FROM dual
UNION SELECT  7,    4,              'div2'         , 4        FROM dual
UNION SELECT  8,    5,              'div3'         , 4        FROM dual
UNION SELECT  9,    5,              'div4'         , 4        FROM dual
UNION SELECT 10,    4,              'div5'         , 4        FROM dual
UNION SELECT 11,    5,              'div6'         , 4        FROM dual
) ;

et ce SQL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT case when niveau >= 1 then nvl(Direction         ,last_value(Direction ignore nulls)         over (order by rn )) 
                             else null end Dir
     , case when niveau >= 2 then nvl(SousDirection     ,last_value(SousDirection ignore nulls)     over (order by rn )) 
                             else null end SDir
     , case when niveau >= 3 then nvl(SousSousDirection ,last_value(SousSousDirection ignore nulls) over (order by rn )) 
                             else null end SSDir
     , case when niveau >= 4 then nvl(Division          ,last_value(Division ignore nulls)          over (order by rn )) 
                             else null end Div
     , id
from (SELECT rn
           , decode( niveau, 1, nom, null ) Direction
           , decode( niveau, 2, nom, null ) SousDirection 
           , decode( niveau, 3, nom, null ) SousSousDirection 
           , decode( niveau, 4, nom, null ) Division
		   , niveau
		   , id
      from (SELECT rownum rn, nom, niveau, id
            FROM service
            connect BY prior id = id_parent
            start WITH id_parent IS NULL ) ) ;

On obtient ce résultat (testé en 11gR1)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
DIR       SDIR      SSDIR     DIV               ID
--------- --------- --------- --------- ----------
direction                                        1
direction s-dir1                                 2
direction s-dir1    ss-dir1                      4
direction s-dir1    ss-dir1   div1               6
direction s-dir1    ss-dir1   div2               7
direction s-dir1    ss-dir1   div5              10
direction s-dir1    ss-dir2                      5
direction s-dir1    ss-dir2   div3               8
direction s-dir1    ss-dir2   div4               9
direction s-dir1    ss-dir2   div6              11
direction s-dir2                                 3

Cela correspond-t-il à ce que vous attendez ?

25/03/2011, 16h03
Go_Ahead

Bonjour,
@ojo77 :
cela fonctionne parfaitement ! S'il serait possible d'avoir un peu d'explication ça serait super.

Un grand merci d'avance à tous pour votre aide (même si je n'ai pas été très clair...)

Les explications :

La première requête imbriquée (ou vue dynamique) est une requête hyérarchique, elle permet d'obtenir en colonne l'organigramme de la société. Ce sont les instruction connect by et start with qui permettent à la requête d'ordonner les lignes hyérarchiquement. On ajoute la colonne rownum (numéro de ligne) pour garder l'ordre hyérérchique en mémoire.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT rownum rn, nom, niveau, id
  2  FROM service
  3  connect BY prior id = id_parent
  4  start WITH id_parent IS NULL
  5  /
 
        RN NOM           NIVEAU         ID
---------- --------- ---------- ----------
         1 direction          1          1
         2 s-dir1             2          2
         3 ss-dir1            3          4
         4 div1               4          6
         5 div2               4          7
         6 div5               4         10
         7 ss-dir2            3          5
         8 div3               4          8
         9 div4               4          9
        10 div6               4         11
        11 s-dir2             2          3

La deuxième requête effectue un simple pivot par rapport à la colonne niveau, en fonction dela valeur de niveau, elle affiche le nom dans une colonne ou dans une autre.

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> SELECT rn
  2       , decode( niveau, 1, nom, NULL ) Direction
  3       , decode( niveau, 2, nom, NULL ) SousDirection
  4       , decode( niveau, 3, nom, NULL ) SousSousDirection
  5       , decode( niveau, 4, nom, NULL ) Division
  6          , niveau
  7          , id
  8  FROM (SELECT rownum rn, nom, niveau, id
  9        FROM service
 10        connect BY prior id = id_parent
 11        start WITH id_parent IS NULL ) ;
 
        RN DIRECTION SOUSDIREC SOUSSOUSD DIVISION      NIVEAU         ID
---------- --------- --------- --------- --------- ---------- ----------
         1 direction                                        1          1
         2           s-dir1                                 2          2
         3                     ss-dir1                      3          4
         4                               div1               4          6
         5                               div2               4          7
         6                               div5               4         10
         7                     ss-dir2                      3          5
         8                               div3               4          8
         9                               div4               4          9
        10                               div6               4         11
        11           s-dir2                                 2          3

La requête finale, ajoute les valeurs souhaitées dans les blancs (nulls) du résultat de la vue dynamique précédente.
en suivant quelques règles :

1°)

On ne remplit toujours la colonne DIRECTION.
On ne remplit la colonne SOUSDIRECTION que si on est sur une ligne qui ne décrit pas une direction
On ne remplit la colonne SOUSSOUSDIRECTION que si on est sur une ligne qui ne décrit ni une direction ni une sous direction
On ne remplit la colonne DIVISION que si on est sur une ligne qui décrit une division

C'est l'instruction case qui s'occupe de gérer ces règles

2°)
Pour remplir une valeur de la table on utilise la dernière valeur calculée non nulle et c'est l'instruction LAST VALUE qui effectue cette partie du travail.