Parcours d'une hierarchie pour trouver un chemin unique (CTE, recursion, ...)

**ghosty177** · 08/03/2011, 09h57

Bonjour a tous,

Je me casse la tête sur un problème depuis hier matin et je ne voie pas du tout comment le résoudre.

Alors je m'explique :
Je dois trouver le chemin unique entre deux tables d'une arborescence de plusieurs dizaines de tables. Ces deux tables pouvant être séparées par plusieurs autre tables.
Je n'ai pour information que le nom de ces deux tables (table départ et arrivée), et, j'ai également en base les enregistrements entre chacune des tables de mon arborescences, notées sous la forme suivante :

Code :

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

TABLE1-TABLE2;RELATION

Le modèle que j'ai a la particularité de n'avoir qu'un seul chemin entre deux tables.

Je pensait donc me servir des CTE afin de réaliser cette recherche mais n'ayant pas en base de lien père-fils cela ne donne rien.

Mon but étant d'avoir a la fin (pas forcément présenté comme cela, c'est pour illustrer) :

Code :

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

TABLE1-TABLEinter1 > TABLEinter1-TABLEinter2 > TABLEinter2-TABLEinter3 > TABLEinter3-TABLE2

edit :
J'ai une idée pour faire cela en code, mais cela impliquerai de lancer plusieurs centaines de requêtes au SGBD et de gros traitement code serveur. C'est pour cela que je souhaiterai le faire a partir du SQL directement.

Quelqu'un aurait il une idée pour m'orienter.

Merci d'avance

Ghosty

**Waldar** · 08/03/2011, 10h44

Il semblerait que vous confondiez plusieurs notions ici.
Une hiérarchie c'est une relation père-fils enregistrée dans la même table, qui pointe donc sur elle-même.

On les interroge avec l'opérateur historique CONNECT BY ou plus récemment depuis 11gR2 avec les CTE récursives.

Maintenant ce que vous avez l'air de rechercher ce sont les associations naturelles de vos tables.
Là il faudrait vous référer à un MCD, mais j'imagine que vous n'en avez pas !

Difficile de vous aider plus, car malheureusement nous ne possédons ni vos tables ni vos données. Si j'ai mal cerné votre problème n'hésitez pas à en dire plus.

**ghosty177** · 08/03/2011, 10h56

Bonjour waldar,

Je ne pense pas que tu ais mal compris, c'a doit plustot etre moi qui me suis mal exprimé...

Enfin bon, effectivement je n'ai pas de mcd a proprement dit car ce que je dois réaliser devra pouvoir s'adapter a plusieurs modèle ne comprenant jamais les même tables.

Je met en pièce jointe un exemple de shema que j'ai a parcourir pour exemple.

Ce shema est une simple représentation graphique.

Moi j'ai a ma disposition :
table1 : SVT-CCLI
table2 : SVT-HCS

En base je vais avoir les éléments suivant :
1 : SVT-CCLI-SVT-CLI;relation
2 : SVT-CLI-SVT-DET;relation
3 : SVT-DET-SVT-HCS: relation
4....
5....
ainsi de suite, pour toutes les relations entre chaque table.

Du coup moi a partir de mes deux tables j'aimerai récupérer mes 3 relations (pour l'exemple ci dessus) en passant par le SQL.

Es-ce que c'est plus clair ??

**xdescamp** · 08/03/2011, 11h15

Bonjour,

Je partirai sur l'approche suivante :
- transforme le champ de relation "TABLE1-TABLE2;RELATION" pour en sortir 2 champs TABLE_PERE ("TABLE1") et TABLE_FILLE ("TABLE2") avec des SUBSTR et INSTR,
- sur cette vue implicite, faire une requête hiérarchique pour établir l'arborescence des dépendances,
- filtrer sur le point de départ et d'arrivée

A la fin, la requête devrait ressembler à quelque chose du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
with liens as (
  select substr (...) table_pere,
         substr (...) table_fille
  from ...)
select *
from (
      select table_pere, table_fille, sys_connect_by_path(table_fille, '-') chemin
      from liens
      connect by prior table_fille = table_pere
      start with table_pere = 'MA_TABLE_PERE_CHERCHEE')
where table_fille = 'MA_TABLE_FILLE_CHERCHEE'

Je n'ai pas testé, donc il y a probablement des erreurs de syntaxe, mais ça doit pouvoir donner une idée de départ.

**ghosty177** · 08/03/2011, 11h21

bonjour et merci xdescamp

Je vais essayer de tester cela.

Mais toutes mes bases n'étant pas en 11gr2, est il possible de faire la même chose avec START WITH ..... CONNECT BY ???

**xdescamp** · 08/03/2011, 11h32

Envoyé par ghosty177

Mais toutes mes bases n'étant pas en 11gr2, est il possible de faire la même chose avec START WITH ..... CONNECT BY ???

CONNECT BY est disponible depuis la 8i il me semble, 9i sûr.

**Waldar** · 08/03/2011, 11h45

CONNECT BY est disponible depuis la v2 !

**ghosty177** · 08/03/2011, 12h08

ok merci pour les info je regarderai ca plus tard.

Je viens de tester la requete proposé par xdescamp et elle me retourne dans tous les cas 0 résultats...
J'ai pris pour test un exemple avec seulement 1 table a trouver entre les deux

Je pense que ca viens (pas certain du tout) de :

Code :

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

connect BY prior table_fille = table_pere

Mais étant donné que je n'aurais jamais la table père et fille pour une relation, il me parait étrange que l'on puisse avoir égale...

Qu'en pensez vous?

La requête exécutée :

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
 
"WITH liens AS " +
"( " +
"SELECT " +
"substr (vuegwsql, 3, 5) table_pere, " +
"substr (vuegwsql, 9) table_fille " +
"FROM gwsql " +
") " +
"SELECT * " +
"FROM " +
"( " +
"SELECT " +
"table_pere, " +
"table_fille, " +
"sys_connect_by_path(table_fille, '/') chemin " +
"FROM liens " +
"CONNECT BY PRIOR table_fille LIKE table_pere " +
"START WITH table_pere = 'SVT-CVF'" +
") " +
"WHERE table_fille = 'SVT-REM' ";

EDIT :
J'ai tester la partie avec les substr et cela me retourne toutes les jointures avec la table père et fille découpée correctement.

EDIT 2 :
En fait je suis sur un base en 10.2. le souci viendrait il de la? pourtant la requête s'exécute sans erreur...

EDIT 3 :
J'ai également oublier de vous dire que les enregistrements de la base ne respecte pas forcément la règle PERE/FILLE dans le nommage. Il y en a également qui sont de type FILLE/PERE.

J'ai donc rajouter au START WITH :

Code :

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

START WITH table_pere = 'SVT-CVF' or table_fille = 'SVT-CVF'

J'ai fait la même chose pour le WHERE de fin

Code :

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

WHERE table_fille = 'SVT-REM' or table_pere = 'SVT-REM'

L'éxécution ne me retourne toujours pas d'enregistrements...

**ghosty177** · 09/03/2011, 10h29

Salut les gars,

alors personne n'a une petite idée?? C'est peu être pas possible...

**Waldar** · 09/03/2011, 10h32

Personnellement, je n'ai toujours pas compris le problème.
Peut-être avec un jeu de test initial et ce que vous en attendez ?

**xdescamp** · 09/03/2011, 11h28

Si le contenu de la table GWSQL n'est pas normalisé, ça ne peut pas fonctionner.
Essayez de repartir de ma requête de départ, mais en remplaçant la vue "liens" par le code suivant pour gérer toutes les combinatoires :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT
substr (vuegwsql, 3, 5) table_pere,
substr (vuegwsql, 9) table_fille
FROM gwsql
UNION
SELECT
substr (vuegwsql, 9) table_pere,
substr (vuegwsql, 3, 5) table_fille
FROM gwsql

Mais attention aux performances.
Mais pour pouvoir vraiment vous aider, il nous faudrait un extrait significatif de la table GWSQL et plusieurs cas de test et de résultat attendu.

**ghosty177** · 09/03/2011, 11h38

Ok pas de souci, je vais essayer de ré-expliquer avec toutes les info.

voila une extraction des informations que j'ai en base concernant une architecture (semblable a l'image jointe plus haut). On retrouve dans cette extraction les liens entre les différentes tables.

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
 
J-SVLCV-SVACS;J-SVLCV-SVACS-S;SVT;svacs.etssvacs = svlcv.etssvlcv and svacs.artsvacs = svlcv.artsvlcv and svacs.actsvacs = 'S';
J-GTETS-SVCDV;Etablissement - Commande;SVT;gtets.numgtets = svcdv.etssvcdv;
J-SVCDV-SKDRE;J-SVCDV-SKDRE;SVT;CDV.nuisvcdv = skdre.nuiskdre;
J-SGUNI-SGART;Unite vente - Article;SVT;Uni_Vte.numsguni += sgart.unvsgart;
J-SKLMS-SGATV;J-SKLMS-SGATV;SVT;sklms.etosklms += sgatv.etssgatv and sklms.artsklms += sgatv.artsgatv;
J-SKLMS-SKMVS;J-SKLMS-SKMVS;SVT;sklms.nuisklms = skmvs.nuiskmvs;
J-SGAAE-SGART;Article Etablissement - Article;SVT;sgart.numsgart = sgaae.artsgaae;
J-OEGES_SGAAE;Article Etablissement - Gestionnaire;SVT;Ges_AEts.numoeges += sgaae.gessgaae;
J-SGMDV_SGAAE;Article Etablissement - Mode vente;SVT;Mode_AEts.numsgmdv += sgaae.movsgaae and Mode_AEts.etssgmdv += sgaae.etssgaae;
J-P_FON_SGAAE;Fonction - Article Etablissement;SVT;FON_AEts_P.occgtpar += sgaae.fonsgaae and FON_AEts_P.numgtets += sgaae.etssgaae and FON_AEts_P.padgtpar += 'FONSGART';
J-P_FNV_SGAAE;Fonction Valo - Article Etablissement;SVT;FNV_AEts_P.occgtpar += sgaae.fnvsgaae and FNV_AEts_P.numgtets += sgaae.etssgaae and FNV_AEts_P.padgtpar += 'FNVSGAAE';
J-P_CAT_SGATV;Categorie - Article vendu;SVT;CAT_AVte_P.occgtpar += sgatv.catsgatv and CAT_AVte_P.numgtets += sgatv.etssgatv and CAT_AVte_P.padgtpar += 'CATSGATV';
J-SGAAE-SGATV;Article Etablissement - Article Vendu;SVT;sgaae.etssgaae = sgatv.etssgatv and sgaae.artsgaae = sgatv.artsgatv;
J-SGATV-OETVA;Article Vendu - TVA;SVT;TVA_AVte.numoetva += sgatv.tvvsgatv and TVA_AVte.etsoetva += sgatv.etssgatv;
J-SGATV-OEGES;Article Vendu - Gestionnaire;SVT;Ges_AVte.numoeges += sgatv.gessgatv;
J-SGATV-SKDRE;J-SGATV-SKDRE;SVT;skdre.etsskdre += sgatv.etssgatv and skdre.artskdre += sgatv.artsgatv;
J-SGATV-SKSTI;J-SGATV-SKSTI;SVT;sksti.artsksti += sgatv.artsgatv;
J-SGATV-SGDEP;Article Vendu - Depot;SVT;Dep_AVte.numsgdep += sgatv.depsgatv and Dep_AVte.etssgdep += sgatv.etssgatv;
J-SGART-SVLCV;J-SGART-SVLCV;SVT;sgart.numsgart = svlcv.artsvlcv;
J-SVCDV-SVCDV;J-SVCDV-SVCDV;SVT;svcdv.ecvsvcdv <= 158;
J-SVCDV-SVNCV;Commande - Classe;SVT;svcdv.clasvcdv = svncv.clasvncv and svcdv.etssvcdv = svncv.etssvncv;
J-SVCDV-SVCLI;Commande - Client;SVT;svcdv.clisvcdv += svcli.numsvcli and svcdv.etssvcdv += svcli.etssvcli;
J-SVCLI-SVECD;Client - Detail Echeance;SVT;sgecd.echsgecd = svcli.echsvcli;
J-SVCDV-SVELC;J-SVCDV-SVELC;SVT;svelc.nuisvelc = svcdv.nuisvcdv;
J-SVCDV-SVEXP;Commande - Expedition;SVT;svexp.numsvexp += svcdv.expsvcdv and svexp.etssvexp += svcdv.etssvcdv;
J-SVCDV-SVFAV;Commande - Facture;SVT;svfav.numsvfav += svcdv.favsvcdv and svfav.etssvfav += svcdv.etssvcdv;
J-SVCDV-SVCVE;Commande - Echeance;SVT;svcve.nuisvcve += svcdv.nuisvcdv;
J-SVCDV-SVLCV;Commande - Ligne;SVT;svlcv.nuisvlcv = svcdv.nuisvcdv;
J-SVCDV-SVCVG;Commande - Gestionnaire;SVT;svcvg.nuisvcvg += svcdv.nuisvcdv and svcvg.fonsvcvg += 'VRP' and svcvg.rolsvcvg += 'V';
J-SVCDV-SGDEP;Commande - Depot;SVT;Dep_Cde.etssgdep = svcdv.etssvcdv and Dep_Cde.numsgdep = svcdv.depsvcdv;
J-SVCDV-SGETC;Commande - Etape;SVT;Etp_Cde.etssgetc = svcdv.etssvcdv and Etp_Cde.etpsgetc = svcdv.ecvsvcdv and Etp_Cde.clasgetc = svcdv.clasvcdv and Etp_Cde.domsgetc = 'V';
J-SVCDV-OEDEV;Commande - Devise;SVT;svcdv.devsvcdv = Dev_Cde.numoedev;
J-SVCVG-OEGES;Gestionnaire commande - Gestionnaire;SVT;oeges.numoeges += svcvg.gessvcvg;
J-SVEXP-SGDEP;Expedition - Depot;SVT;Dep_Exp.etssgdep = svexp.etssvexp and Dep_Exp.numsgdep = svexp.depsvexp;

On peut voir que chaque enregistrement est construit comme cela :
IDENTIFIANT;DESCRIPTION;RELATION

A partir de cela je voudrais récupérer (par une requête SQL) le chemin entre SVFAV et SGART
A savoir :

Code :

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

J-SVCDV-SVFAV > J-SVCDV-SVLCV > J-SGART-SVLCV

Autre exemple avec SVEXP et GTEST

Code :

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

J-SVCDV-SVEXP ; J-GTETS-SVCDV

et un exemple simple (même si celui la j'arrive a le récupérer par une requete simple), SVCDV et SVETC

Code :

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

J-SVCDV-SGETC

Cela me permettra ensuite de générer la partie WHERE d'une requête SQL, donc l'ordre de récupération n'a pas d'importance.

Si tu veux d'autre explications n'hésite pas?

**xdescamp** · 09/03/2011, 16h26

Comme prévu, ce n'est pas performant, mais voici une requête qui fonctionne :

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
55
56
57
WITH gwsql AS (
	SELECT 'J-SVLCV-SVACS' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-GTETS-SVCDV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SKDRE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGUNI-SGART' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SKLMS-SGATV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SKLMS-SKMVS' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGAAE-SGART' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-OEGES_SGAAE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGMDV_SGAAE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-P_FON_SGAAE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-P_FNV_SGAAE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-P_CAT_SGATV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGAAE-SGATV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGATV-OETVA' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGATV-OEGES' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGATV-SKDRE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGATV-SKSTI' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGATV-SGDEP' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SGART-SVLCV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVCDV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVNCV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVCLI' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCLI-SVECD' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVELC' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVEXP' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVFAV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVCVE' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVLCV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SVCVG' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SGDEP' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-SGETC' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCDV-OEDEV' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVCVG-OEGES' AS vuegwsql FROM dual UNION ALL
	SELECT 'J-SVEXP-SGDEP' AS vuegwsql FROM dual
	),
liens AS (
	SELECT
		SUBSTR (vuegwsql, 3, 5) table_pere,
		SUBSTR (vuegwsql, 9) table_fille,
        vuegwsql
	FROM gwsql
	UNION
	SELECT
		SUBSTR (vuegwsql, 9) table_pere,
		SUBSTR (vuegwsql, 3, 5) table_fille,
        vuegwsql
	FROM gwsql
	)
SELECT MIN (chemin) KEEP (DENSE_RANK FIRST ORDER BY nb_etapes ASC)
FROM (
      SELECT table_fille, SYS_CONNECT_BY_PATH(vuegwsql, ' > ') chemin, LEVEL nb_etapes
      FROM liens
      CONNECT BY NOCYCLE PRIOR table_fille = table_pere
                     AND PRIOR table_pere != table_fille
      START WITH table_pere = 'SVFAV')
WHERE table_fille = 'SGART'

Testée en version 10.2.

**Waldar** · 09/03/2011, 16h53

J'ai une solution relativement similaire :

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
With Matable as
(
select 'J-SVLCV-SVACS' as vuegwsql, 'J-SVLCV-SVACS-S'                 as c2, 'SVT' as c3, 'svacs.etssvacs = svlcv.etssvlcv AND svacs.artsvacs = svlcv.artsvlcv AND svacs.actsvacs = ''S'''                                      as whr from dual union all 
select 'J-GTETS-SVCDV'            , 'Etablissement - Commande'             , 'SVT'      , 'gtets.numgtets = svcdv.etssvcdv'                                                                                                            from dual union all
select 'J-SVCDV-SKDRE'            , 'J-SVCDV-SKDRE'                        , 'SVT'      , 'CDV.nuisvcdv = skdre.nuiskdre'                                                                                                              from dual union all
select 'J-SGUNI-SGART'            , 'Unite vente - Article'                , 'SVT'      , 'Uni_Vte.numsguni += sgart.unvsgart'                                                                                                         from dual union all
select 'J-SKLMS-SGATV'            , 'J-SKLMS-SGATV'                        , 'SVT'      , 'sklms.etosklms += sgatv.etssgatv AND sklms.artsklms += sgatv.artsgatv'                                                                      from dual union all
select 'J-SKLMS-SKMVS'            , 'J-SKLMS-SKMVS'                        , 'SVT'      , 'sklms.nuisklms = skmvs.nuiskmvs'                                                                                                            from dual union all
select 'J-SGAAE-SGART'            , 'Article Etablissement - Article'      , 'SVT'      , 'sgart.numsgart = sgaae.artsgaae'                                                                                                            from dual union all
select 'J-OEGES_SGAAE'            , 'Article Etablissement - Gestionnaire' , 'SVT'      , 'Ges_AEts.numoeges += sgaae.gessgaae'                                                                                                        from dual union all
select 'J-SGMDV_SGAAE'            , 'Article Etablissement - Mode vente'   , 'SVT'      , 'Mode_AEts.numsgmdv += sgaae.movsgaae AND Mode_AEts.etssgmdv += sgaae.etssgaae'                                                              from dual union all
select 'J-P_FON_SGAAE'            , 'Fonction - Article Etablissement'     , 'SVT'      , 'FON_AEts_P.occgtpar += sgaae.fonsgaae AND FON_AEts_P.numgtets += sgaae.etssgaae AND FON_AEts_P.padgtpar += ''FONSGART'''                    from dual union all
select 'J-P_FNV_SGAAE'            , 'Fonction Valo - Article Etablissement', 'SVT'      , 'FNV_AEts_P.occgtpar += sgaae.fnvsgaae AND FNV_AEts_P.numgtets += sgaae.etssgaae AND FNV_AEts_P.padgtpar += ''FNVSGAAE'''                    from dual union all
select 'J-P_CAT_SGATV'            , 'Categorie - Article vendu'            , 'SVT'      , 'CAT_AVte_P.occgtpar += sgatv.catsgatv AND CAT_AVte_P.numgtets += sgatv.etssgatv AND CAT_AVte_P.padgtpar += ''CATSGATV'''                    from dual union all
select 'J-SGAAE-SGATV'            , 'Article Etablissement - Article Vendu', 'SVT'      , 'sgaae.etssgaae = sgatv.etssgatv AND sgaae.artsgaae = sgatv.artsgatv'                                                                        from dual union all
select 'J-SGATV-OETVA'            , 'Article Vendu - TVA'                  , 'SVT'      , 'TVA_AVte.numoetva += sgatv.tvvsgatv AND TVA_AVte.etsoetva += sgatv.etssgatv'                                                                from dual union all
select 'J-SGATV-OEGES'            , 'Article Vendu - Gestionnaire'         , 'SVT'      , 'Ges_AVte.numoeges += sgatv.gessgatv'                                                                                                        from dual union all
select 'J-SGATV-SKDRE'            , 'J-SGATV-SKDRE'                        , 'SVT'      , 'skdre.etsskdre += sgatv.etssgatv AND skdre.artskdre += sgatv.artsgatv'                                                                      from dual union all
select 'J-SGATV-SKSTI'            , 'J-SGATV-SKSTI'                        , 'SVT'      , 'sksti.artsksti += sgatv.artsgatv'                                                                                                           from dual union all
select 'J-SGATV-SGDEP'            , 'Article Vendu - Depot'                , 'SVT'      , 'Dep_AVte.numsgdep += sgatv.depsgatv AND Dep_AVte.etssgdep += sgatv.etssgatv'                                                                from dual union all
select 'J-SGART-SVLCV'            , 'J-SGART-SVLCV'                        , 'SVT'      , 'sgart.numsgart = svlcv.artsvlcv'                                                                                                            from dual union all
select 'J-SVCDV-SVCDV'            , 'J-SVCDV-SVCDV'                        , 'SVT'      , 'svcdv.ecvsvcdv <= 158'                                                                                                                      from dual union all
select 'J-SVCDV-SVNCV'            , 'Commande - Classe'                    , 'SVT'      , 'svcdv.clasvcdv = svncv.clasvncv AND svcdv.etssvcdv = svncv.etssvncv'                                                                        from dual union all
select 'J-SVCDV-SVCLI'            , 'Commande - Client'                    , 'SVT'      , 'svcdv.clisvcdv += svcli.numsvcli AND svcdv.etssvcdv += svcli.etssvcli'                                                                      from dual union all
select 'J-SVCLI-SVECD'            , 'Client - Detail Echeance'             , 'SVT'      , 'sgecd.echsgecd = svcli.echsvcli'                                                                                                            from dual union all
select 'J-SVCDV-SVELC'            , 'J-SVCDV-SVELC'                        , 'SVT'      , 'svelc.nuisvelc = svcdv.nuisvcdv'                                                                                                            from dual union all
select 'J-SVCDV-SVEXP'            , 'Commande - Expedition'                , 'SVT'      , 'svexp.numsvexp += svcdv.expsvcdv AND svexp.etssvexp += svcdv.etssvcdv'                                                                      from dual union all
select 'J-SVCDV-SVFAV'            , 'Commande - Facture'                   , 'SVT'      , 'svfav.numsvfav += svcdv.favsvcdv AND svfav.etssvfav += svcdv.etssvcdv'                                                                      from dual union all
select 'J-SVCDV-SVCVE'            , 'Commande - Echeance'                  , 'SVT'      , 'svcve.nuisvcve += svcdv.nuisvcdv'                                                                                                           from dual union all
select 'J-SVCDV-SVLCV'            , 'Commande - Ligne'                     , 'SVT'      , 'svlcv.nuisvlcv = svcdv.nuisvcdv'                                                                                                            from dual union all
select 'J-SVCDV-SVCVG'            , 'Commande - Gestionnaire'              , 'SVT'      , 'svcvg.nuisvcvg += svcdv.nuisvcdv AND svcvg.fonsvcvg += ''VRP'' AND svcvg.rolsvcvg += ''V'''                                                 from dual union all
select 'J-SVCDV-SGDEP'            , 'Commande - Depot'                     , 'SVT'      , 'Dep_Cde.etssgdep = svcdv.etssvcdv AND Dep_Cde.numsgdep = svcdv.depsvcdv'                                                                    from dual union all
select 'J-SVCDV-SGETC'            , 'Commande - Etape'                     , 'SVT'      , 'Etp_Cde.etssgetc = svcdv.etssvcdv AND Etp_Cde.etpsgetc = svcdv.ecvsvcdv AND Etp_Cde.clasgetc = svcdv.clasvcdv AND Etp_Cde.domsgetc = ''V''' from dual union all
select 'J-SVCDV-OEDEV'            , 'Commande - Devise'                    , 'SVT'      , 'svcdv.devsvcdv = Dev_Cde.numoedev'                                                                                                          from dual union all
select 'J-SVCVG-OEGES'            , 'Gestionnaire commande - Gestionnaire' , 'SVT'      , 'oeges.numoeges += svcvg.gessvcvg'                                                                                                           from dual union all
select 'J-SVEXP-SGDEP'            , 'Expedition - Depot'                   , 'SVT'      , 'Dep_Exp.etssgdep = svexp.etssvexp AND Dep_Exp.numsgdep = svexp.depsvexp'                                                                    from dual
)
  ,  sr1 as
(
select substr(vuegwsql, 3, 5) as table1,
       substr(vuegwsql, -5)   as table2,
       vuegwsql,
       whr
  from matable
 union   
select substr(vuegwsql, -5),
       substr(vuegwsql, 3, 5),
       vuegwsql,
       whr
  from matable
)
  ,  sr2 as
(
 select level, table1, table2, whr,
        ltrim(sys_connect_by_path(vuegwsql, ' -> '), ' -> ') as chemin
   from sr1
  where table2      = 'SVFAV' 
  START with table1 = 'SGART'
CONNECT BY NOCYCLE table1 = prior table2
  order by level asc
)
select table1, table2, whr, chemin
  from sr2
 where rownum = 1;
 
TABLE1 TABLE2 WHR                                                                     CHEMIN
------ ------ ----------------------------------------------------------------------- -----------------------------------------------
SVCDV  SVFAV  svfav.numsvfav += svcdv.favsvcdv AND svfav.etssvfav += svcdv.etssvcdv   J-SGART-SVLCV -> J-SVCDV-SVLCV -> J-SVCDV-SVFAV

**ghosty177** · 09/03/2011, 17h09

Pouahllalala les gars, franchement c'est ouf je m'attendais pas à un truc comme ca.
Mais ca marche, respect.

Par contre j'aimerai bien comprendre...

Alors déja la première partie ou vous reprenais toute la table (c'est crazy), mais c'est quoi le but on peut pas se servir de la table en elle meme directement? et les "FROM dual UNION ALL" ca sert a quoi (edit : en fait c'est le "dual" que je comprend pas trop, il sort d'où)?

sinon quelque chose m'intrigue encore plus c'est que la requete de Waldar s'exécute instantanément alors que celle de xdescamp mais un temps considérable. Pourtant je ne vois pas de différence flagrante (edit : pour moi ca viendrai du Connect by, mais pourquoi... ).

**xdescamp** · 09/03/2011, 17h45

Envoyé par ghosty177

Alors déja la première partie ou vous reprenais toute la table (c'est crazy), mais c'est quoi le but on peut pas se servir de la table en elle meme directement? et les "FROM dual UNION ALL" ca sert a quoi (edit : en fait c'est le "dual" que je comprend pas trop, il sort d'où)?

Le problème est que nous n'avons pas la table, il n'y a que toi qui y a accès! Du coup, on simule son existence et ses données avec cette astuce. Dual quant à elle est une table système don la caractéristique est d'avoir un et un seul enregistrement :

Envoyé par ghosty177

sinon quelque chose m'intrigue encore plus c'est que la requete de Waldar s'exécute instantanément alors que celle de xdescamp mais un temps considérable. Pourtant je ne vois pas de différence flagrante (edit : pour moi ca viendrai du Connect by, mais pourquoi... ).

Il faudrait comparer les plans d'exécution. la différence vient peut-être de l'endroit où est fait le filtre sur la table finale.

**ghosty177** · 09/03/2011, 17h54

d'accord je commence a comprendre la première partie donc j'ai pas besoin de faire un bout de programme pour générer ca, il suffit que je remplace les FROM par ma table a moi et que j'y ajoute mes filtres. enfin si j'ai bien compris.

Sinon pour ce qui est de l'exécution, j'ai simplement copier vos requêtes et je les exécutes tel quelle, je n'ai ajouter aucun filtre.
La je n'ai pas accès au plan d'exécution mais je vous récupère ca demain matin.

**Waldar** · 09/03/2011, 18h00

À mon avis il suffit de mettre en commentaire ceci :

Code :

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

AND PRIOR table_pere != table_fille

Vu qu'il y a déjà un NOCYCLE qui empêche les boucles, je pense qu'il vaut mieux conserver le critère récursif le plus simple possible.

**ghosty177** · 09/03/2011, 18h15

Effectivement je viens de remplacer la partie haute de la requête de Waldar et j'ai remplacer les valeurs suivantes par celle de ma base et ca marche au poil.

J'ai fait la même chose avec celle de xdescamp et j'ai également enlever ce qu'a dit Waldar et la requête s'exécute instantanément aussi.

Résultat requête Waldar :

Code :

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

J-SGART-SVLCV -> J-SVCDV-SVLCV -> J-SVCDV-SVFAV

Résultat requête xdescamp :

Code :

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

 > J-SVCDV-SVFAV > J-SVCDV-SVLCV > J-SGART-SVLCV

Bizarrement, les requêtes ne tournent pas dans le même sens et celle de xdescamp semble me retourner un enregistrement nul en première place.

Je pourrais récupérer les plans d'exécution demain matin si ca vous intéresse.
Sinon je garderai la requête de Waldar. Dans tous les cas un grand merci a vous deux.

**Waldar** · 09/03/2011, 18h41

Vu la structure de votre table, c'est la même chose que faire de A --> B --> C ou C --> B --> A.

Sinon il ne faut pas remplacer la première partie par vos valeurs, vous les avez déjà dans votre table, il suffit de commencer la deuxième étape.

Parcours d'une hierarchie pour trouver un chemin unique (CTE, recursion, ...)

SQL Oracle

Discussions similaires

Partager

Partager