[oracle9i] Requête hiérarchique

Version imprimable

Bonjour.

Parmi les nombreux posts sur les requêtes hiérarchiques, je n'ai pas trouvé un cas similaire au mien. En effet, j'ai une table contenant toutes les relations entre un père et tous ses descendants, y compris lui-même.
Et j'aimerais afficher une arborescence classique.

Prenons cet arbre pour exemple (l'arbre que j'aimerais afficher):

Code:

1
2
3
4
5
6
7
8
9
10
11
A
--A1
----A11
------A111
------A112
----A12
----A13
--A2
----A21
----A22
------A221

Voici ce que j'ai en base pour le schéma ci-dessus:

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
WITH t AS (
          SELECT 'A' PARENT, 'A' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A1' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A2' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A11' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A12' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A13' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A21' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A22' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A111' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A112' enfant FROM dual
UNION ALL SELECT 'A' PARENT, 'A221' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A1' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A11' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A12' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A13' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A111' enfant FROM dual
UNION ALL SELECT 'A1' PARENT, 'A112' enfant FROM dual
UNION ALL SELECT 'A2' PARENT, 'A2' enfant FROM dual
UNION ALL SELECT 'A2' PARENT, 'A21' enfant FROM dual
UNION ALL SELECT 'A2' PARENT, 'A22' enfant FROM dual
UNION ALL SELECT 'A2' PARENT, 'A221' enfant FROM dual
UNION ALL SELECT 'A11' PARENT, 'A11' enfant FROM dual
UNION ALL SELECT 'A11' PARENT, 'A111' enfant FROM dual
UNION ALL SELECT 'A11' PARENT, 'A112' enfant FROM dual
UNION ALL SELECT 'A12' PARENT, 'A12' enfant FROM dual
UNION ALL SELECT 'A13' PARENT, 'A13' enfant FROM dual
UNION ALL SELECT 'A21' PARENT, 'A21' enfant FROM dual
UNION ALL SELECT 'A22' PARENT, 'A22' enfant FROM dual
UNION ALL SELECT 'A22' PARENT, 'A221' enfant FROM dual
UNION ALL SELECT 'A111' PARENT, 'A111' enfant FROM dual
UNION ALL SELECT 'A112' PARENT, 'A112' enfant FROM dual
UNION ALL SELECT 'A221' PARENT, 'A221' enfant FROM dual
)

J'ai bien essayé avec un connect by prior et un SYS_CONNECT_BY_PATH mais il y a toujours un truc qui coince.

Merci pour votre aide.

Qu'est-ce qui ne convient pas avec la boucle standard ?

Code:

1
2
3
4
select lpad('-', 2*(level-1), '-') || enfant as resultat
from t
start with parent = enfant
connect by nocycle prior enfant = parent

31/07/2009, 14h37
GoLDoZ

Ce qui ne va pas, c'est que... je ne suis pas en 10 g mais en 9i. :oops:

Du coup, le NOCYCLE ne passe pas, et si je l'enlève, j'ai l'erreur "boucle CONNECT BY dans les données utilisateur" (erreur que j'ai avec ma requête, proche de la vôtre).

Peut être que tu pourrais éviter le nocycle en transformant les données sur le modelé de la table emp : à la place du parent = enfant, parent is null et utilisation d’un inline view et un case
Code:

1 2 3 4 5 SELECT lpad('-', 2*(level-1), '-') || enfant AS resultat FROM (Select Case when parent = enfant then null else parent end parent, enfant from t) connect BY prior enfant = parent start WITH parent is null

31/07/2009, 15h45
GoLDoZ

On y est presque. ;)
Votre requête m'affiche l'arborescence de A (lignes 1 à 25 du résultat), mais aussi celle de A1 (26-33), A2 (34-38), A11...... jusqu'à A221. Toutes les arborescences des fils, même sans descendants.
Seules les 25 premières lignes - descendance de A - m'intéressent.

Salut !

Prends le level max pour chaque enfant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
, u AS (
SELECT level as l, enfant, lpad('-', 2*(level-1), '-') || enfant AS resultat
FROM (SELECT Case when parent = enfant then NULL else parent end parent, enfant FROM t)
connect BY prior enfant = parent
start WITH parent IS NULL
)
, v as(
SELECT resultat, row_number() over(partition by enfant order by l desc) rk
from u)
select * 
from v 
where rk = 1

Vous pouvez le faire ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT resultat
FROM
(
SELECT
    lpad('-', 2*(level-1), '-') || enfant AS resultat,
    substr(sys_connect_by_path(enfant, '/'), 2) AS path
FROM
    (SELECT case parent when enfant then NULL else parent end parent, enfant FROM t)
start WITH parent IS NULL
connect BY prior enfant = parent
)
WHERE 'A' in (path, substr(path, 1, instr(path, '/')-1))

En 10g+ c'est équivalent à :

Code:

1
2
3
4
5
    select lpad('-', 2*(level-1), '-') || enfant as resultat
      from t
     where connect_by_root enfant = 'A'
start with parent = enfant
connect by nocycle prior enfant = parent