Discussion :

[bguihal]

Voila je suis en 9i et je fait des requetes hiérarchique sur une table, jusque la pas de soucci.
Mon arbres est constitué d'elements qui ont tous un délai, j'aimerai cumulé ces délais sur mes branches les plus hautes.
il s'agit donc de trouver un chemin critique.
exemple :

Code :

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

1
2
3
            0     
          1   2     
        11     21

si tous les element ont 1 en délai, j'aimerai une requete qui me ramenne :
sur le 11 et le 21 : 1 sur le 1et 2 : 2 et sur le 0 : 3

es ce possible en 1 seul requete en 9i ?

merci.

[ojo77]

Désolé ... erreur de compréhension de ma part

[laurentschneider]

• test data

Code :

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

1
2
3
4
5
6
create table t as (
        select  0 child,null parent from dual union all
        select 1,0 from dual union all
        select 2,0 from dual union all
        select 11,1 from dual union all
        select 21,2 from dual )

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
select child, max(l) from
(select child, level l  
from t 
connect by prior parent=child 
)
group by child
order by 2
 
     CHILD     MAX(L)
---------- ----------
        21          1
        11          1
         1          2
         2          2
         0          3

[mnitu]

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With data as
  2  (
  3    Select 0 As id, null as parent_id, 0 as delai from dual union all
  4    Select 1 As id, 0 as parent_id, 1 as delai from dual union all
  5    Select 2 As id, 0 as parent_id, 2 as delai from dual union all
  6    Select 11 As id, 1 as parent_id, 2 as delai from dual union all
  7    Select 21 As id, 2 as parent_id, 3 as delai from dual
  8  )
  9  Select id, parent_id, (Select Sum(delai)
 10                           From data b
 11                          Where id In (Select id
 12                                         From data c
 13                                        connect by prior id = parent_id
 14                                        start with id = a.id)
 15                        ) Sum_delai
 16    From data a
 17  /
 
        ID  PARENT_ID  SUM_DELAI
---------- ---------- ----------
         0                     8
         1          0          3
         2          0          5
        11          1          2
        21          2          3
 
SQL>

Mais dpv perf ça peut être vite la Berezina.

[bguihal]

en fait c'est le chemin crique qu'il me faut, pas le total de tous :

Code :

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

1
2
3
4
5
6
7
     ID  PARENT_ID  SUM_DELAI
---------- ---------- ----------
         0                     8
         1          0          3
         2          0          5
        11          1          2
        21          2          3

donc pour le 0 ce serai 0+1+2 (coté element 1) soit 3
et 0+2+3 (coté element 2) soit 5
donc un total de 5.
quand t'on a une seul branche t'a requete marche tres bien.

pour laurentschneider merci, mais mon probleme est la somme des délais.

[mnitu]

Maintenant c'est Waterloo

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
54
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> WITH DATA AS
  2  (
  3    SELECT 0 AS id, NULL AS parent_id, 0 AS delai FROM dual union ALL
  4    SELECT 1 AS id, 0 AS parent_id, 1 AS delai FROM dual union ALL
  5    SELECT 2 AS id, 0 AS parent_id, 2 AS delai FROM dual union ALL
  6    SELECT 11 AS id, 1 AS parent_id, 2 AS delai FROM dual union ALL
  7    SELECT 21 AS id, 2 AS parent_id, 3 AS delai FROM dual union ALL
  8    SELECT 100 AS id, NULL AS parent_id, 0 AS delai FROM dual union ALL
  9    SELECT 101 AS id, 100 AS parent_id, 7 AS delai FROM dual union ALL
 10    SELECT 102 AS id, 100 AS parent_id, 1 AS delai FROM dual union ALL
 11    SELECT 1011 AS id, 101 AS parent_id, 4 AS delai FROM dual union ALL
 12    SELECT 1021 AS id, 102 AS parent_id, 8 AS delai FROM dual
 13  ),
 14  Calc_Data As
 15  (
 16    SELECT id, parent_id, (SELECT Sum(delai)
 17                             FROM DATA b
 18                            WHERE id IN (SELECT id
 19                                           FROM DATA c
 20                                          connect BY prior id = parent_id
 21                                          start WITH id = a.id)
 22                           ) Sum_delai
 23      FROM DATA a
 24  )
 25  Select id, parent_id,
 26         Case When parent_id Is Null Then (Select Max(Sum_delai)
 27                                             From Calc_Data b
 28                                            Where parent_id = a.id)
 29         Else Sum_Delai
 30         End delai
 31    From Calc_data a
 32  /
 
        ID  PARENT_ID      DELAI
---------- ---------- ----------
         0                     5
         1          0          3
         2          0          5
        11          1          2
        21          2          3
       100                    11
       101        100         11
       102        100          9
      1011        101          4
      1021        102          8
 
10 rows selected
 
SQL>

[laurentschneider]

avec 2 petites fonctions (en 10g il y aurait le connect_by_root)

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION eval (str VARCHAR2)
   RETURN NUMBER
IS
   n   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :n := ' || str || ';end;' USING OUT n;
 
   RETURN n;
END;
/

Code :

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

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION root (str VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (str, 2, INSTR (str || '/', '/', 2) - 2);
END;
/

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
WITH t
     AS (SELECT 0 id, NULL parent, 0 delai FROM DUAL
         UNION ALL
         SELECT 1, 0, 1 FROM DUAL
         UNION ALL
         SELECT 2, 0, 2 FROM DUAL
         UNION ALL
         SELECT 11, 1, 2 FROM DUAL
         UNION ALL
         SELECT 21, 2, 3 FROM DUAL)
  SELECT root, MAX (PATH)
    FROM (    SELECT root (SYS_CONNECT_BY_PATH (id, '/')) root,
                     eval (SYS_CONNECT_BY_PATH (delai, '+')) PATH
                FROM t
          CONNECT BY parent = PRIOR id)
GROUP BY root;

Code :

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

1
2
3
4
5
6
7
ROOT MAX(PATH)
---- ---------
1            3
11           2
21           3
0            5
2            5