[Oracle 9i] Requête hiérarchique (connect by)

Bonjour.

J'ai un souci pour effectuer une requête hiérarchique. J'ai bien essayé de reproduire l'exemple "classique" mais je n'ai pas tout à fait les mêmes tables.

Car je peux (et je veux ;) ) effectuer la requête de deux façons, à partir de deux tables différentes.

La première table est construite comme un arbre où les pères sont reliés à tous leurs descendants y compris à eux-mêmes, mais (là est la différence avec l'exemple classique) il n'y a pas de colonne vide. Exemple de données:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

PERE_ID FILS_ID A1 A1 A1 A11 A1 A12 A1 A13 A1 A111 A1 A112 A1 A121 A2 A2 A2 A21 A2 A22 A11 A11 A11 A111 A11 A112 A12 A12 A12 A121 A13 A13 A111 A111 A112 A112 A121 A121

---

La seconde table est une table de lien direct père-fils, dans laquelle les pères ne sont pas liés à eux-mêmes et où il n'y a toujours pas de colonne vide. Exemple:

Code:

---

1 2 3 4 5 6 7 8 9

PERE_ID FILS_ID A1 A11 A1 A12 A1 A13 A2 A21 A2 A22 A11 A111 A11 A112 A12 A121

---

Je me trouve confronté soit à l'erreur "Boucle connect by dans les données utilisateur", soit à une requête sans fin. A noter que NOCYCLE et PARENT n'ont pas l'air d'être reconnus en 9i.


Pour la première table, j'ai testé quelque chose comme ça:

Code:

---

1 2 3 4 5

SELECT LPAD ('+', 2 * (LEVEL - 1), '+') || pere_id FROM table1 WHERE pere_id <> fils_id START WITH pere_id = 'A1' CONNECT BY PRIOR fils_id = pere_id

---

J'ai précisé 'A1' pour tester unitairement, sachant que je peux (veux) partir d'un ensemble donné de pères (et donc remplacer 'A1' par ma_vue.pere_id, ma_vue étant dans la même requête).

Pour la seconde table, je n'ai encore rien trouvé de concluant.

Bref, je sens que quelque chose m'échappe... Merci du coup de main! :D

Pour la première requête elle fonctionne en filtrant le where avant le connect by :

Code:

---

1 2 3 4

SELECT level, LPAD ('+', 2 * (LEVEL - 1), '+') || pere_id, fils_id FROM (SELECT * FROM TABLE1 WHERE PERE_ID <> FILS_ID) START WITH pere_id = 'A1' CONNECT BY PRIOR fils_id = pere_id;

---

Je n'ai pas regardé pour la seconde.

Qu'est-ce qui vous embête au fait ?
La syntaxe de la seconde paraît simple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

with table2 as ( select 'A1' as pere_id, 'A11' as fils_id from dual union all select 'A1' , 'A12' from dual union all select 'A1' , 'A13' from dual union all select 'A2' , 'A21' from dual union all select 'A2' , 'A22' from dual union all select 'A11' , 'A111' from dual union all select 'A11' , 'A112' from dual union all select 'A12' , 'A121' from dual ) select substr(sys_connect_by_path(pere_id, ' '), 2) || ' ' || fils_id as chemin from table2 start with pere_id = 'A1' connect by prior fils_id = pere_id;   CHEMIN A1 A11 A1 A11 A111 A1 A11 A112 A1 A12 A1 A12 A121 A1 A13

---

Bonjour Waldar. Merci pour vos réponses.

Pour la seconde, cela fonctionne sans le sys_connect_by_path (dans votre exemple). En quoi celui-ci est-il indispensable?

Je ne pense pas qu'il soit indispensable, je l'ai juste mis pour vérifier le chemin parcouru.

Pour la première requête, j'ai retrouvé un code que j'avais déjà travaillé:

Code:

---

1 2 3 4

SELECT LPAD('-', 2*(level-1), '-') || fils AS resultat FROM (SELECT CASE WHEN pere = fils THEN NULL ELSE pere end pere, fils FROM table1) START WITH pere IS NULL CONNECT BY PRIOR fils = pere

---

Mais j'ai des fils répétés mal positionnés.

Il me reste à travailler un peu ces codes pour formater le résultat sur une colonne sans répétitions des fils (sans la partie en bleu):

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

RESULTAT A --A1 ----A11 ------A111 ------A112 ----A12 --A2 ----A21 ----A22 ------A221 A1 --A11 ----A111 ----A112 --A12 A2 --A21 --A22 ----A221

---

:ccool:

Un premier résultat.
Dans le second cas que j'évoquais, j'ai réussi à obtenir exactement ce que je voulais, à savoir une arborescence totale des données, sans doublons.

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

WITH table1 AS ( SELECT 'A1' AS pere, 'A11' AS fils FROM dual union ALL SELECT 'A1' , 'A12' FROM dual union ALL SELECT 'A1' , 'A13' FROM dual union ALL SELECT 'A2' , 'A21' FROM dual union ALL SELECT 'A2' , 'A22' FROM dual union ALL SELECT 'A11' , 'A111' FROM dual union ALL SELECT 'A11' , 'A112' FROM dual union ALL SELECT 'A12' , 'A121' FROM dual union ALL SELECT 'A21' , 'A211' FROM dual union ALL SELECT 'A21' , 'A212' FROM dual union ALL SELECT 'A121' , 'A1211' FROM dual union ALL SELECT 'A112' , 'A1121' FROM dual ), table2 AS (SELECT * FROM table1 t UNION ALL SELECT DISTINCT NULL pere, t.pere fils FROM table1 t WHERE NOT EXISTS (SELECT 1 FROM table1 t2 WHERE t2.fils = t.pere)) SELECT SUBSTR (SYS_CONNECT_BY_PATH (pere, ' '), 2) || ' ' || fils AS chemin FROM table2 START WITH pere IS NULL CONNECT BY PRIOR fils = pere;

---

Résultat:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CHEMIN A1 A1 A11 A1 A11 A111 A1 A11 A112 A1 A11 A112 A1121 A1 A12 A1 A12 A121 A1 A12 A121 A1211 A1 A13 A2 A2 A21 A2 A21 A211 A2 A21 A212 A2 A22

---

Pour mon premier cas, autrement dit à partir de cela:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

WITH table1 AS ( SELECT 'A1' pere, 'A1' fils FROM dual UNION ALL SELECT 'A1' pere, 'A11' fils FROM dual UNION ALL SELECT 'A1' pere, 'A12' fils FROM dual UNION ALL SELECT 'A1' pere, 'A13' fils FROM dual UNION ALL SELECT 'A1' pere, 'A111' fils FROM dual UNION ALL SELECT 'A1' pere, 'A112' fils FROM dual UNION ALL SELECT 'A1' pere, 'A121' fils FROM dual UNION ALL SELECT 'A11' pere, 'A11' fils FROM dual UNION ALL SELECT 'A11' pere, 'A111' fils FROM dual UNION ALL SELECT 'A11' pere, 'A112' fils FROM dual UNION ALL SELECT 'A12' pere, 'A12' fils FROM dual UNION ALL SELECT 'A12' pere, 'A121' fils FROM dual UNION ALL SELECT 'A13' pere, 'A13' fils FROM dual UNION ALL SELECT 'A111' pere, 'A111' fils FROM dual UNION ALL SELECT 'A112' pere, 'A112' fils FROM dual UNION ALL SELECT 'A121' pere, 'A121' fils FROM dual UNION ALL SELECT 'A2' pere, 'A2' fils FROM dual UNION ALL SELECT 'A2' pere, 'A21' fils FROM dual UNION ALL SELECT 'A2' pere, 'A22' fils FROM dual UNION ALL SELECT 'A21' pere, 'A21' fils FROM dual UNION ALL SELECT 'A22' pere, 'A22' fils FROM dual)

---

je n'arrive pas à obtenir une arborescence du même type.
La recherche continue... :cfou: