Requête "doublement" récursive [Résolu]

[Manopower]

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 !

[Waldar]

Il manque les données de départ !

[Manopower]

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 !

[Waldar]

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

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

[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

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

[Waldar]

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.

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

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

[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

et puis ça mets 6 sec sur mon poste, ptete que sur le super système multi trucs de prod ça va dépoter !

[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 , pour une table de départ a 21.000 enregistrements.