Requête "doublement" récursive

**Manopower** · 16/12/2010, 11h49

Bonjour,
voici le contexte.

J'ai une table collaborateur, qui a un Type_collab. Chaque collab a un "chef" qui est lui même un collab

un collab de type ingénieur a forcément dans sa hierarchie un collab de type chef de projet.

un collab de type chef de projet a forcément dans sa hierarchie un collab de type manager.

un collab de type directeur n'a pas de chef (idHierarchie = null)

Le problème c'est qu'un ingénieur peut avoir dans sa hierarchie un ingénieur senior entre lui et son chef de projet, voir même un chef d'équipe ...
Le chef de projet peut lui être rattaché directement au manager ou bien avoir un directeur de projet...

Je sais écrire la requête afin de récupérer le chef de projet de chaque ingénieur.
Je sais écrire la requête afin de récupérer le manager de chaque chef de projet

J'ai besoin de votre aide pour écrire la requête me permettant de connaitre le chef de projet et son manager pour chaque ingénieur !

Code :

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

1
2
3
4
5
6
7
8
SELECT c.nom  AS ingenieur,
        connect_by_root c.nom AS ChefDeProjet
FROM Collab c, Type_collab t
WHERE c.idtypCollab = t.idTypCollab 
  AND t.codTypeCollab not in ('CP','MANAGER')
  AND connect_by_isleaf = 1   
START WITH t.codTypeCollab = 'CP'
CONNECT BY PRIOR c.idCollab = c.idHierarchie

Résultat obtenu :

Code :

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

1
2
3
4
5
6
7
8
9
 
Ingenieur  ChefDeProjet
Bob         Michel
Luke        Michel
Franck      Michel
Julie         Simon
Sylvain      Simon 
Rachid      Benoit
Sophie      Benoit

Résultat attendu :

Code :

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

1
2
3
4
5
6
7
8
9
 
Ingenieur  ChefDeProjet  Manager
Bob          Michel           François
Luke         Michel           François
Franck      Michel           François
Julie         Simon            Jérôme
Sylvain     Simon            Jérôme
Rachid      Benoit           François
Sophie      Benoit           François

Si vous avez une idée pour faire ça, je suis preneur !
merci d'avance !

**Waldar** · 16/12/2010, 11h56

Il manque les données de départ !

**Manopower** · 16/12/2010, 15h13

En effet,
voici le jeu d'essai concernant les résultats indiqués dans le premier message.

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
Table Collab join TypeCollab
id nom libelleType id_Hierarchie
1 Bob   Ingénieur      10
2 Luke  Ingénieur      8
3 Franck  Ingénieur    10
4 Julie Ingénieur        12
5 Sylvain Ingénieur     11 
6 Rachid Ingénieur     13
7 Sophie Ingénieur     13
8 Suzanne IngéSenior 9
9 Fred ChefEquipe 10
10 Michel CP 15
11 Lucie ChefEquipe 12
12 Simon CP 16
13 Benoit CP 14
14 Mathieu DirecteurProjet 15
15 Francois Manager 17
16 Jérome Manager 17
17 Xavier Directeur null

merci !

**Waldar** · 16/12/2010, 18h16

J'ai utilisé cette solution là, pas complètement satisfaisante car j'ai supposé que CP était toujours juste en-dessous de Manager :

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
35
36
37
38
39
40
With MaTable as
(
select  1 as id, 'Bob' as nom, 'Ingénieur' as libelleType, 10 as id_Hierarchie from dual union all
select  2      , 'Luke'      , 'Ingénieur'               ,  8                  from dual union all
select  3      , 'Franck'    , 'Ingénieur'               , 10                  from dual union all
select  4      , 'Julie'     , 'Ingénieur'               , 12                  from dual union all
select  5      , 'Sylvain'   , 'Ingénieur'               , 11                  from dual union all
select  6      , 'Rachid'    , 'Ingénieur'               , 13                  from dual union all
select  7      , 'Sophie'    , 'Ingénieur'               , 13                  from dual union all
select  8      , 'Suzanne'   , 'IngéSenior'              ,  9                  from dual union all
select  9      , 'Fred'      , 'ChefEquipe'              , 10                  from dual union all
select 10      , 'Michel'    , 'CP'                      , 15                  from dual union all
select 11      , 'Lucie'     , 'ChefEquipe'              , 12                  from dual union all
select 12      , 'Simon'     , 'CP'                      , 16                  from dual union all
select 13      , 'Benoit'    , 'CP'                      , 15                  from dual union all
select 14      , 'Mathieu'   , 'DirecteurProjet'         , 15                  from dual union all
select 15      , 'Francois'  , 'Manager'                 , 17                  from dual union all
select 16      , 'Jérome'    , 'Manager'                 , 17                  from dual union all
select 17      , 'Xavier'    , 'Directeur'               , null                from dual
)
    SELECT mt.nom,
           substr(      sys_connect_by_path( mt.nom, '/' ),
                  instr(sys_connect_by_path( mt.nom, '/' ), '/', 1, 2) + 1,
                  instr(sys_connect_by_path( mt.nom, '/' ), '/', 1, 3) - instr(sys_connect_by_path( mt.nom, '/' ), '/', 1, 2) - 1) as cp,
           connect_by_root mt.nom as Manager
      FROM MaTable mt
     WHERE mt.libelleType = 'Ingénieur'
START WITH mt.libelleType = 'Manager'
CONNECT BY PRIOR mt.id = mt.id_Hierarchie
  ORDER BY mt.id asc;
 
NOM      CP     MANAGER 
-------- ------ --------
Bob      Michel Francois
Luke     Michel Francois
Franck   Michel Francois
Julie    Simon  Jérome  
Sylvain  Simon  Jérome  
Rachid   Benoit Francois
Sophie   Benoit Francois

**Manopower** · 17/12/2010, 15h12

En effet, de mon coté je n'ai pas trouvé mieux, mais ça ne répond pas au besoin initial puisque CP et Manager peuvent être reliés via un DP ou plus hélas.

En attendant un miracle, je fais 2 requêtes Ingé > CP et CP > Manager et je boucle dans le code Java.

Merci pour la tentative Waldar !

**Waldar** · 17/12/2010, 15h15

Quitte à faire les deux requêtes, autant faire la jointure dans le SQL :

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
WITH MaTable AS
(
SELECT  1 AS id, 'Bob' AS nom, 'Ingénieur' AS libelleType, 10 AS id_Hierarchie FROM dual union ALL
SELECT  2      , 'Luke'      , 'Ingénieur'               ,  8                  FROM dual union ALL
SELECT  3      , 'Franck'    , 'Ingénieur'               , 10                  FROM dual union ALL
SELECT  4      , 'Julie'     , 'Ingénieur'               , 12                  FROM dual union ALL
SELECT  5      , 'Sylvain'   , 'Ingénieur'               , 11                  FROM dual union ALL
SELECT  6      , 'Rachid'    , 'Ingénieur'               , 13                  FROM dual union ALL
SELECT  7      , 'Sophie'    , 'Ingénieur'               , 13                  FROM dual union ALL
SELECT  8      , 'Suzanne'   , 'IngéSenior'              ,  9                  FROM dual union ALL
SELECT  9      , 'Fred'      , 'ChefEquipe'              , 10                  FROM dual union ALL
SELECT 10      , 'Michel'    , 'CP'                      , 15                  FROM dual union ALL
SELECT 11      , 'Lucie'     , 'ChefEquipe'              , 12                  FROM dual union ALL
SELECT 12      , 'Simon'     , 'CP'                      , 16                  FROM dual union ALL
SELECT 13      , 'Benoit'    , 'CP'                      , 15                  FROM dual union ALL
SELECT 14      , 'Mathieu'   , 'DirecteurProjet'         , 15                  FROM dual union ALL
SELECT 15      , 'Francois'  , 'Manager'                 , 17                  FROM dual union ALL
SELECT 16      , 'Jérome'    , 'Manager'                 , 17                  FROM dual union ALL
SELECT 17      , 'Xavier'    , 'Directeur'               , NULL                FROM dual
)
  ,  Manager as
(
    SELECT mt.id, mt.nom,
           connect_by_root mt.nom AS Manager
      FROM MaTable mt
     WHERE mt.libelleType = 'Ingénieur'
START WITH mt.libelleType = 'Manager'
CONNECT BY PRIOR mt.id = mt.id_Hierarchie
)
  ,  CP as
(
    SELECT mt.id, mt.nom,
           connect_by_root mt.nom AS CP
      FROM MaTable mt
     WHERE mt.libelleType = 'Ingénieur'
START WITH mt.libelleType = 'CP'
CONNECT BY PRIOR mt.id = mt.id_Hierarchie
)
select m.nom, c.cp, m.manager
  from CP c
       inner join Manager m
         on m.id = c.id
order by m.id asc;
 
NOM      CP     MANAGER 
-------- ------ --------
Bob      Michel Francois
Luke     Michel Francois
Franck   Michel Francois
Julie    Simon  Jérome  
Sylvain  Simon  Jérome  
Rachid   Benoit Francois
Sophie   Benoit Francois

Requête "doublement" récursive

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager