Requête "doublement" récursive

Version imprimable

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:

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:

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:

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 !

16/12/2010, 11h56
Waldar

Il manque les données de départ !

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

Code:

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 !

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

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
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

17/12/2010, 15h12
Manopower

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 !

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

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
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

20/12/2010, 11h31
Manopower

Bonjour,
merci pour cette solution, j'approche du but !

J'ai essayé de mettre ton code dans un vue pour une utilisation en Java mais la clause With dans la création de vue ne semble pas passer...

Pas de soucis :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace view v_test
as 
with toto as
(
select dummy as col from dual
)
select col from toto;
-- View created.
 
select col from v_test;
COL
---
  X
-- 1 row selected.
 
drop view v_test;
-- View dropped.

20/12/2010, 11h56
Manopower

ça y est ça fonctionne !

j'ai repris dans l'autre sens ta proposition et j'ai supprimé le With.

dans ta dernière requête, dans la clause FROM j'ai remplacé le nom des tables par la requête correspondante.

Bon, 6 secondes d'exécution mais j'ai mes résultats, comme les perf ne sont pas un prérequis pour ce batch, l'objectif est atteint.

Merci !
20/12/2010, 12h25
Waldar

En comparaison à la boucle Java, que donnent les performances ?

Si la vue est souvent appelée et que les données sont relativement fixes, ça peut être utile de regarder du côté des vues matérialisées.
20/12/2010, 13h36
Manopower

La vue n'est appelée qu'une fois par mois, la nuit, dans le cadre d'un batch qui durera près d'une heure, donc 6 secondes de plus ou de moins, c'est pas dérangeant, merci :ccool:

et puis ça mets 6 sec sur mon poste, ptete que sur le super système multi trucs de prod ça va dépoter ! :aie:
20/12/2010, 14h10
Manopower

Avec un peu de d'améliorations c'est encore mieux !

Je ne ramène plus les libellés dans ma vue mais uniquement les id (sur lesquels il y a des indexes) donc je ne provoque plus de full scan sur la table. Résultat, au lieu de 6 sec, ça mets 0,4 sec sur mon poste :ccool:, pour une table de départ a 21.000 enregistrements.