Discussion :

[GoLDoZ]

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 :

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

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

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 :

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

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!

[Waldar]

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

Code :

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

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.

[Waldar]

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

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

[GoLDoZ]

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?

[Waldar]

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

[GoLDoZ]

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

Code :

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

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 :

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
RESULTAT
A
--A1
----A11
------A111
------A112
----A12
--A2
----A21
----A22
------A221
A1
--A11
----A111
----A112
--A12
A2
--A21
--A22
----A221

[GoLDoZ]

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 :

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

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

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 :

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