Discussion :

[ghosty177]

Bonjour à tous,

Je viens vers vous car je suis entrain de travaillé sur la récursivité sur une base SQL Server.
Et je dois adapter des requêtes oracle pour cette base de données. Pour la grande majorité j'y suis arrivé sans souci seulement je me retrouve face à une requête oracle utilisant la récursivité (Start with ... connect by...).

Voici la requête en question qui fonctionne bien sur Oracle.

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
WITH matable AS ( 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 										FROM dual UNION ALL 
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 												FROM dual UNION ALL 
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	FROM dual UNION ALL 
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	FROM dual UNION ALL 
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 										FROM dual 
), 
sr1 AS ( 
SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable 
), 
sr2 AS ( 
SELECT level, table1, table2, sys_connect_by_path(sql, ' AND ') AS chemin FROM sr1 WHERE table2 = 'SVFAV'  
START WITH table1 = 'SGART' CONNECT BY NOCYCLE table1 = PRIOR table2 ORDER BY level ASC 
) 
SELECT chemin FROM sr2 WHERE rownum = 1

N'arrivant pas à l'adapter malgré mes recherche et le cours se sqlpro sur le forum, je me tourne vers vous.

Merci d'avance

Ghosty

[elsuket]

Bonjour,

Je pense ne pas être loin de la solution mais l'expression de la récursivité sous Oracle est un peu cryptique pour moi.
Pouvez-vous m'indiquer :

- quelle colonne de l'expression de table commune matable constitue le parent
- quelle colonne de l'expression de table commune matable constitue l'enfant
- quelle est la valeur du parent ou de l'enfant qui est la racine de la hiérarchie ?
- quelle est la valeur du parent ou de l'enfant qui est la feuille de la hiérarchie ?

@++

[ghosty177]

Bonjour,

alors en fait c'est justement la le soucis, c'est que je n'est pas de lien parent ou enfant.
C'est pour cela que je fais un union all qui inverse les tables, cela me permet de couvrir ainsi tout le champ d'action du réseau de table.

Le but étant de trouver le plus court chemin entre deux tables et de concaténer la partie sql afin de me fournir les relation entre les table.

Par contre je ne voit pas ce que tu veux dire par racine et feuille.

Pour l'exemple de la requête le résultat escompter est le suivant entre SVFAV et SGART :

Code :

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

sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv

un autre exemple entre GTETS et SVLCV

Code :

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

gtets.numgtets = svcdv.etssvcdv AND svlcv.nuisvlcv = svcdv.nuisvcdv

En espérant t'avoir orienter si tu as d'autres question n'hésite pas.

[aieeeuuuuu]

Bonjour

il y a surement plus optimisé, mais avec cette requête, on ne doit pas être loin du but:

Code SQL :

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
 
 
;WITH matable AS ( 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, CAST('gtets.numgtets = svcdv.etssvcdv' AS VARCHAR(100)) AS sql 				UNION ALL 
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 										 UNION ALL 
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 												 UNION ALL 
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	 UNION ALL 
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	 UNION ALL 
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 										 UNION ALL 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 										 UNION ALL 
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 										 UNION ALL 
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 										 
), 
sr1 AS ( 
SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable 
), 
sr2 AS (
	SELECT 
		CAST(1 AS INT) as L,
		table1,
		table2,
		CAST(sr1.sql AS VARCHAR(MAX)) AS chemin,
		CAST(table1 AS VARCHAR(MAX)) +'->' + table2  + ' | 'as rte
	FROM sr1
	WHERE  table1 = 'GTETS'
	UNION ALL
	SELECT 
		sr2.L + 1,
		sr1.table1,
		sr1.table2,
		CAST(sr2.chemin AS VARCHAR(MAX)) + CAST(' AND ' AS VARCHAR(5)) + CAST(sr1.sql AS VARCHAR(MAX)),
		rte + CAST(sr1.table1 AS VARCHAR(MAX)) +'->' + sr1.table2 + ' | '
	FROM sr2
	INNER JOIN  sr1 
		ON sr2.table2 = sr1.table1
		AND sr2.rte NOT LIKE '%->' + sr1.table2 + ' |%'--sr1.table2 <> sr2.table1 => pour eviter les boucles infinie
	WHERE NOT EXISTS(
		SELECT *
		FROM sr1
		WHERE sr1.table1 = sr2.table2
			AND sr1.table2 =  'SVLCV'  
		)
	) 
	SELECT --il reste a jouter la derniere etape, non incluse car condition d'arret de la recursion
	rte,
		sr2.L + 1 as niveau,
		sr1.table1,
		sr1.table2,
		CAST(sr2.chemin AS VARCHAR(MAX)) + CAST(' AND ' AS VARCHAR(5)) + CAST(sr1.sql AS VARCHAR(MAX)) as chemin
	FROM sr2
		INNER JOIN  sr1 
		ON sr2.table2 = sr1.table1
			AND  sr1.table2 = 'SVLCV'  
OPTION (MAXRECURSION 0)

[ghosty177]

WooW !!
Je viens de faire quelques test ça a l'air de rouler...!!
Je comprend pas tout ce que tu as fait, mais je te remercie. Je crois pas que j'aurais pu arriver à ça tout seul.


edit : Que veut tu dire par :

il reste a jouter la derniere etape, non incluse car condition d'arret de la recursion

Sinon quand tu dis qu'il y a plus optimisé que celle la... j'ose pas imaginer l'optimisation ma requête oracle avec ses deux petites lignes...

Merci

[aieeeuuuuu]

Bah en fait :

1/ Je pars du point de départ (jusque là rien d'extraordinaire )
2/ par recursion, je cherche toutes les "bifurcation" possibles
3/ condition d’arrêt : j’arrête la récursion lorsqu'il existe dans la table sr1, une ultime étape pour arriver à bon port (cette étape n'est donc pas incluse dans sr2, et c'est pourquoi je la rajoute dans la requête finale). C'est le but du "NOT EXISTS" dans la partie recursive

Il faut également s'assurer qu'on ne boucle pas... c'est l'objet de la clause de jointure avec le LIKE. Il n'est pas possible d'appeler plusieurs fois la CTE dans la partie récursive, donc au fur et à mesure de la récursion, je "stocke" dans un varchar la liste des étapes, et je vérifie pour chaque bifurcation que la partie récursive va ajouter, sije ne suis pas déjà passé par la destination(en vérifiant si la destination n'est pas dans la chaine). D’ailleurs dans cette chaine, je stock départ et arrivé (table 1 et table2) ce qui est inutile (on pourrait donc déjà commencer par là pour optimiser)

Cependant, cette vérification pour éviter les boucles ne semble pas indispensable, puisque de toute façon la récursion s’arrête dès qu'on a trouvé un chemin(elle permet quand même d'éviter de traiter ces boucles inutiles). Cependant sans cela, et si on lance une requête sans chemin possible, la requête tournera indéfiniment (ou plutôt jusqu’à épuisement du serveur )

revoici la requête avec l’amélioration sur la vérification de boucle (ce qui au passage ne doit pas non plus changer la face du monde )

Code SQL :

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
 
;WITH matable AS ( 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, CAST('gtets.numgtets = svcdv.etssvcdv' AS VARCHAR(100)) AS sql 				UNION ALL 
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 										 UNION ALL 
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 												 UNION ALL 
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	 UNION ALL 
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	 UNION ALL 
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 										 UNION ALL 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 										 UNION ALL 
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 										 UNION ALL 
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 										 
), 
sr1 AS ( 
SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable 
), 
sr2 AS (
	SELECT 
		CAST(1 AS INT) as L,
		table1,
		table2,
		CAST(sr1.sql AS VARCHAR(MAX)) AS chemin,
		CAST(table1 AS VARCHAR(MAX)) +'->' as rte
	FROM sr1
	WHERE  table1 = 'GTETS'
	UNION ALL
	SELECT 
		sr2.L + 1,
		sr1.table1,
		sr1.table2,
		CAST(sr2.chemin AS VARCHAR(MAX)) + CAST(' AND ' AS VARCHAR(5)) + CAST(sr1.sql AS VARCHAR(MAX)),
		rte + CAST(sr1.table1 AS VARCHAR(MAX)) +'->' 
	FROM sr2
	INNER JOIN  sr1 
		ON sr2.table2 = sr1.table1
		AND sr2.rte NOT LIKE '%' + sr1.table2 + '->%'--sr1.table2 <> sr2.table1 => pour eviter les boucles infinie
	WHERE NOT EXISTS(
		SELECT *
		FROM sr1
		WHERE sr1.table1 = sr2.table2
			AND sr1.table2 =  'SVLCV'  
		)
	) 
	SELECT --il reste a jouter la derniere etape, non incluse car condition d'arret de la recursion
	rte,
		sr2.L + 1 as niveau,
		sr1.table1,
		sr1.table2,
		CAST(sr2.chemin AS VARCHAR(MAX)) + CAST(' AND ' AS VARCHAR(5)) + CAST(sr1.sql AS VARCHAR(MAX)) as chemin
	FROM sr2
		INNER JOIN  sr1 
		ON sr2.table2 = sr1.table1
			AND  sr1.table2 = 'SVLCV'  
OPTION (MAXRECURSION 0)