Recursivité CTE avec SQL Server [Résolu]

ghosty177 · 08/08/2011, 12h10

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 · 10/08/2011, 10h13

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 · 10/08/2011, 11h19

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 · 10/08/2011, 16h33

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 · 10/08/2011, 17h15

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 :

Citation:

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 · 10/08/2011, 17h51

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)

ghosty177 · 11/08/2011, 15h58

salut,

merci pour les explications, j'y vois déja un peu plus clair.

Sinon en faisant des nouveaux test, il s'avère que la requête ne me renvoie pas les bons résultats. En fait, c'est pas tout à fait ça car il me retourne le bon résultat et il y ajoute des chemins en rapport avec la "table2" passée en paramètre.

Voila les données utilisé avec la requête :

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
WITH matable AS ( 
	SELECT 'ETA_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ETA_CCol_P.occgtpar = Cla_Col.etagwsql and ETA_CCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'GEN_CCol_P.occgtpar = Cla_Col.gengwsql and GEN_CCol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'LAN_CCol_P.occgtpar = Cla_Col.langwsql and LAN_CCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CCOL_P' AS table1, 'CLA_COL' AS table2, 'NAT_CCol_P.occgtpar = Cla_Col.natgwsql and NAT_CCol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ROL_CCol_P.occgtpar = Cla_Col.rolgwsql and ROL_CCol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_CCOL_P' AS table1, 'CLA_COL' AS table2, 'TYP_CCol_P.occgtpar = Cla_Col.typgwsql and TYP_CCol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'USR_CCOL' AS table2, 'Cla_Col.ucrgwsql = Usr_CCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CCOL' AS table1, 'CLA_COL' AS table2, 'Udm_CCol.numgtusr += Cla_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ETA_ECol_P.occgtpar = Exp_Col.etagwsql and ETA_ECol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'GEN_ECol_P.occgtpar = Exp_Col.gengwsql and GEN_ECol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'LAN_ECol_P.occgtpar = Exp_Col.langwsql and LAN_ECol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_ECOL_P' AS table1, 'EXP_COL' AS table2, 'NAT_ECol_P.occgtpar = Exp_Col.natgwsql and NAT_ECol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ROL_ECol_P.occgtpar = Exp_Col.rolgwsql and ROL_ECol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_ECOL_P' AS table1, 'EXP_COL' AS table2, 'TYP_ECol_P.occgtpar = Exp_Col.typgwsql and TYP_ECol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'USR_ECOL' AS table2, 'Exp_Col.ucrgwsql = Usr_ECol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_ECOL' AS table1, 'EXP_COL' AS table2, 'Udm_ECol.numgtusr += Exp_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ETA_EFil_P.occgtpar = Exp_Fil.etagwsql and ETA_EFil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'GEN_EFil_P.occgtpar = Exp_Fil.gengwsql and GEN_EFil_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'LAN_EFil_P.occgtpar = Exp_Fil.langwsql and LAN_EFil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'NAT_EFil_P.occgtpar = Exp_Fil.natgwsql and NAT_EFil_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ROL_EFil_P.occgtpar = Exp_Fil.rolgwsql and ROL_EFil_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'TYP_EFil_P.occgtpar = Exp_Fil.typgwsql and TYP_EFil_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_FIL' AS table1, 'USR_EFIL' AS table2, 'Exp_Fil.ucrgwsql = Usr_EFil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EFIL' AS table1, 'EXP_FIL' AS table2, 'Udm_EFil.numgtusr += Exp_Fil.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ETA_EJoi_P.occgtpar = Exp_Joi.etagwsql and ETA_EJoi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'GEN_EJoi_P.occgtpar = Exp_Joi.gengwsql and GEN_EJoi_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'NAT_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'NAT_EJoi_P.occgtpar = Exp_Joi.natgwsql and NAT_EJoi_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ROL_EJoi_P.occgtpar = Exp_Joi.rolgwsql and ROL_EJoi_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'TYP_EJoi_P.occgtpar = Exp_Joi.typgwsql and TYP_EJoi_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'USR_EJOI' AS table2, 'Exp_Joi.ucrgwsql = Usr_EJoi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EJOI' AS table1, 'EXP_JOI' AS table2, 'Udm_EJoi.numgtusr += Exp_Joi.udmgwsql' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'VUE_JOI' AS table2, 'Exp_Joi.vuegwsql = Vue_Joi.numgwvue and Exp_Joi.metgwsql = Vue_Joi.metgwvue and Exp_Joi.langwsql = Vue_Joi.langwvue' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'GWCOL' AS table2, 'Cla_Col.metgwsql += gwcol.metgwcol and Cla_Col.vuegwsql += gwcol.vuegwcol and  Cla_Col.langwsql += gwcol.langwcol and Cla_Col.texgwsql += ''C'' and Cla_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'GWCOL' AS table2, 'Exp_Col.metgwsql += gwcol.metgwcol and Exp_Col.vuegwsql += gwcol.vuegwcol and Exp_Col.langwsql += gwcol.langwcol and Exp_Col.texgwsql += ''E'' and Exp_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'AGR_COL_P' AS table1, 'GWCOL' AS table2, 'AGR_Col_P.occgtpar = gwcol.agrgwcol and AGR_Col_P.padgtpar = ''AGRGWCOL''' AS sql UNION ALL 
	SELECT 'ETA_COL_P' AS table1, 'GWCOL' AS table2, 'ETA_Col_P.occgtpar = gwcol.etagwcol and ETA_Col_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_COL_P' AS table1, 'GWCOL' AS table2, 'GEN_Col_P.occgtpar = gwcol.gengwcol and GEN_Col_P.padgtpar = ''GENGWCOL''' AS sql UNION ALL 
	SELECT 'LAN_COL_P' AS table1, 'GWCOL' AS table2, 'LAN_Col_P.occgtpar = gwcol.langwcol and LAN_Col_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_COL_P' AS table1, 'GWCOL' AS table2, 'NAT_Col_P.occgtpar = gwcol.natgwcol and NAT_Col_P.padgtpar = ''NATGWCOL''' AS sql UNION ALL 
	SELECT 'QUA_COL_P' AS table1, 'GWCOL' AS table2, 'QUA_Col_P.occgtpar = gwcol.quagwcol and QUA_Col_P.padgtpar = ''QUAGWCOL''' AS sql UNION ALL 
	SELECT 'ROL_COL_P' AS table1, 'GWCOL' AS table2, 'ROL_Col_P.occgtpar = gwcol.rolgwcol and ROL_Col_P.padgtpar = ''ROLGWCOL''' AS sql UNION ALL 
	SELECT 'TYP_COL_P' AS table1, 'GWCOL' AS table2, 'TYP_Col_P.occgtpar = gwcol.typgwcol and TYP_Col_P.padgtpar = ''TYPGWCOL''' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'USR_COL' AS table2, 'gwcol.ucrgwcol = Usr_Col.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_COL' AS table1, 'GWCOL' AS table2, 'Udm_Col.numgtusr += gwcol.udmgwcol' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'NUI_COL' AS table2, 'gwcol.metgwcol = Nui_Col.metgwcol and gwcol.vuegwcol = Nui_Col.vuegwcol and gwcol.langwcol = Nui_Col.langwcol and gwcol.modgwcol = Nui_Col.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_CTX_P' AS table1, 'GWCTX' AS table2, 'ETA_Ctx_P.occgtpar = GWCTX.etagwctx and ETA_Ctx_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CTX_P' AS table1, 'GWCTX' AS table2, 'GEN_Ctx_P.occgtpar = GWCTX.gengwctx and GEN_Ctx_P.padgtpar = ''GENGWCTX''' AS sql UNION ALL 
	SELECT 'LAN_CTX_P' AS table1, 'GWCTX' AS table2, 'LAN_Ctx_P.occgtpar = GWCTX.langwctx and LAN_Ctx_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CTX_P' AS table1, 'GWCTX' AS table2, 'NAT_Ctx_P.occgtpar = GWCTX.natgwctx and NAT_Ctx_P.padgtpar = ''NATGWCTX''' AS sql UNION ALL 
	SELECT 'ROL_CTX_P' AS table1, 'GWCTX' AS table2, 'ROL_Ctx_P.occgtpar = GWCTX.rolgwctx and ROL_Ctx_P.padgtpar = ''ROLGWCTX''' AS sql UNION ALL 
	SELECT 'TYP_CTX_P' AS table1, 'GWCTX' AS table2, 'TYP_Ctx_P.occgtpar = GWCTX.typgwctx and TYP_Ctx_P.padgtpar = ''TYPGWCTX''' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'USR_CTX' AS table2, 'gwctx.ucrgwctx = Usr_Ctx.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CTX' AS table1, 'GWCTX' AS table2, 'Udm_Ctx.numgtusr += gwctx.udmgwctx' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'GWMET' AS table2, 'gwctx.metgwctx = gwmet.numgwmet and gwctx.langwctx = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'ETA_DEM_P' AS table1, 'GWDEM' AS table2, 'ETA_Dem_P.occgtpar = gwdem.etagwdem and ETA_Dem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_DEM_P' AS table1, 'GWDEM' AS table2, 'GEN_Dem_P.occgtpar = gwdem.gengwdem and GEN_Dem_P.padgtpar = ''GENGWDEM''' AS sql UNION ALL 
	SELECT 'LAN_DEM_P' AS table1, 'GWDEM' AS table2, 'LAN_Dem_P.occgtpar = gwdem.langwdem and Lan_Dem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_DEM_P' AS table1, 'GWDEM' AS table2, 'NAT_Dem_P.occgtpar = gwdem.natgwdem and NAT_Dem_P.padgtpar = ''NATGWDEM''' AS sql UNION ALL 
	SELECT 'ROL_DEM_P' AS table1, 'GWDEM' AS table2, 'ROL_Dem_P.occgtpar = gwdem.rolgwdem and ROL_Dem_P.padgtpar = ''ROLGWDEM''' AS sql UNION ALL 
	SELECT 'TVA_DEM_P' AS table1, 'GWDEM' AS table2, 'TVA_Dem_P.occgtpar = gwdem.tvagwdem and TVA_Dem_P.padgtpar = ''TVAGWDEM''' AS sql UNION ALL 
	SELECT 'TYP_DEM_P' AS table1, 'GWDEM' AS table2, 'TYP_Dem_P.occgtpar = gwdem.typgwdem and TYP_Dem_P.padgtpar = ''TYPGWDEM''' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'USR_DEM' AS table2, 'gwdem.ucrgwdem = Usr_Dem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_DEM' AS table1, 'GWDEM' AS table2, 'Udm_Dem.numgtusr += gwdem.udmgwdem' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'VUE_DEM' AS table2, 'gwdem.vuegwdem = Vue_Dem.numgwvue and gwdem.metgwdem = Vue_Dem.metgwvue and gwdem.langwdem = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'COL_HIE' AS table2, 'gwhie.metgwhie = Col_Hie.metgwcol and gwhie.langwhie = Col_Hie.langwcol and gwhie.ncogwhie = Col_Hie.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_HIE_P' AS table1, 'GWHIE' AS table2, 'ETA_Hie_P.occgtpar = gwhie.etagwhie and ETA_Hie_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_HIE_P' AS table1, 'GWHIE' AS table2, 'GEN_Hie_P.occgtpar = gwhie.gengwhie and GEN_Hie_P.padgtpar = ''GENGWHIE''' AS sql UNION ALL 
	SELECT 'LAN_HIE_P' AS table1, 'GWHIE' AS table2, 'LAN_Hie_P.occgtpar = gwhie.langwhie and LAN_Hie_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_HIE_P' AS table1, 'GWHIE' AS table2, 'NAT_Hie_P.occgtpar = gwhie.natgwhie and NAT_Hie_P.padgtpar = ''NATGWHIE''' AS sql UNION ALL 
	SELECT 'ROL_HIE_P' AS table1, 'GWHIE' AS table2, 'ROL_Hie_P.occgtpar = gwhie.rolgwhie and ROL_Hie_P.padgtpar = ''ROLGWHIE''' AS sql UNION ALL 
	SELECT 'TYP_HIE_P' AS table1, 'GWHIE' AS table2, 'TYP_Hie_P.occgtpar = gwhie.typgwhie and TYP_Hie_P.padgtpar = ''TYPGWHIE''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'USR_HIE' AS table2, 'gwhie.ucrgwhie = Usr_Hie.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_HIE' AS table1, 'GWHIE' AS table2, 'Udm_Hie.numgtusr += gwhie.udmgwhie' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'GWMET' AS table2, 'gwhie.metgwhie = gwmet.numgwmet and gwhie.langwhie = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GTAPP' AS table2, 'gwmet.appgwmet = gtapp.numgtapp' AS sql UNION ALL 
	SELECT 'ETA_MET_P' AS table1, 'GWMET' AS table2, 'ETA_Met_P.occgtpar = gwmet.etagwmet and ETA_Met_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_MET_P' AS table1, 'GWMET' AS table2, 'GEN_Met_P.occgtpar = gwmet.gengwmet and GEN_Met_P.padgtpar = ''GENGWMET''' AS sql UNION ALL 
	SELECT 'LAN_MET_P' AS table1, 'GWMET' AS table2, 'LAN_Met_P.occgtpar = gwmet.langwmet and LAN_Met_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_MET_P' AS table1, 'GWMET' AS table2, 'NAT_Met_P.occgtpar = gwmet.natgwmet and NAT_Met_P.padgtpar = ''NATGWMET''' AS sql UNION ALL 
	SELECT 'ROL_MET_P' AS table1, 'GWMET' AS table2, 'ROL_Met_P.occgtpar = gwmet.rolgwmet and ROL_Met_P.padgtpar = ''ROLGWMET''' AS sql UNION ALL 
	SELECT 'TYP_MET_P' AS table1, 'GWMET' AS table2, 'TYP_Met_P.occgtpar = gwmet.typgwmet and TYP_Met_P.padgtpar = ''TYPGWMET''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'USR_MET' AS table2, 'gwmet.ucrgwmet = Usr_Met.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_MET' AS table1, 'GWMET' AS table2, 'Udm_Met.numgtusr += gwmet.udmgwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GES_MET' AS table2, 'gwmet.gesgwmet = Ges_Met.numoeges' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_COL' AS table2, 'gwmet.numgwmet = Vue_Col.metgwvue and gwmet.langwmet = Vue_Col.langwvue and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_DEM' AS table2, 'gwmet.numgwmet = Vue_Dem.metgwvue and gwmet.langwmet = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_FIL' AS table2, 'gwmet.numgwmet = Vue_Fil.metgwvue and gwmet.langwmet = Vue_Fil.langwvue and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_JOI' AS table2, 'gwmet.numgwmet = Vue_Joi.metgwvue and gwmet.langwmet = Vue_Joi.langwvue and Vue_Joi.tvugwvue = ''J''' AS sql UNION ALL 
	SELECT 'ETA_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ETA_VCol_P.occgtpar = Vue_Col.etagwvue and ETA_VCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'GEN_VCol_P.occgtpar = Vue_Col.gengwvue and GEN_VCol_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'LAN_VCol_P.occgtpar = Vue_Col.langwvue and LAN_VCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VCOL_P' AS table1, 'VUE_COL' AS table2, 'NAT_VCol_P.occgtpar = Vue_Col.natgwvue and NAT_VCol_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ROL_VCol_P.occgtpar = Vue_Col.rolgwvue and ROL_VCol_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VCOL_P' AS table1, 'VUE_COL' AS table2, 'TYP_VCol_P.occgtpar = Vue_Col.typgwvue and TYP_VCol_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'USR_VCOL' AS table2, 'Vue_Col.ucrgwvue = Usr_VCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VCOL' AS table1, 'VUE_COL' AS table2, 'Udm_VCol.numgtusr += Vue_Col.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GWCOL' AS table2, 'Vue_Col.metgwvue = gwcol.metgwcol and Vue_Col.langwvue = gwcol.langwcol and Vue_Col.numgwvue = gwcol.vuegwcol and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GES_VCOL' AS table2, 'Vue_Col.gesgwvue = Ges_VCol.numoeges' AS sql UNION ALL 
	SELECT 'ETA_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ETA_VDem_P.occgtpar = Vue_Dem.etagwvue and ETA_VDem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'GEN_VDem_P.occgtpar = Vue_Dem.gengwvue and GEN_VDem_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'LAN_VDem_P.occgtpar = Vue_Dem.langwvue and LAN_VDem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'NAT_VDem_P.occgtpar = Vue_Dem.natgwvue and NAT_VDem_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ROL_VDem_P.occgtpar = Vue_Dem.rolgwvue and ROL_VDem_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'TYP_VDem_P.occgtpar = Vue_Dem.typgwvue and TYP_VDem_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'USR_VDEM' AS table2, 'Vue_Dem.ucrgwvue = Usr_VDem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VDEM' AS table1, 'VUE_DEM' AS table2, 'Udm_VDem.numgtusr += Vue_Dem.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'GES_VDEM' AS table2, 'Vue_Dem.gesgwvue = Ges_VDem.numoeges' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'EXP_FIL' AS table2, 'Vue_Fil.metgwvue = Exp_Fil.metgwsql and Vue_Fil.langwvue = Exp_Fil.langwsql and Vue_Fil.numgwvue = Exp_Fil.vuegwsql and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'ETA_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ETA_Fil_P.occgtpar = Vue_Fil.etagwvue and ETA_Fil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'GEN_Fil_P.occgtpar = Vue_Fil.gengwvue and GEN_Fil_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'LAN_Fil_P.occgtpar = Vue_Fil.langwvue and LAN_Fil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_FIL_P' AS table1, 'VUE_FIL' AS table2, 'NAT_Fil_P.occgtpar = Vue_Fil.natgwvue and NAT_Fil_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ROL_Fil_P.occgtpar = Vue_Fil.rolgwvue and ROL_Fil_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_FIL_P' AS table1, 'VUE_FIL' AS table2, 'TYP_Fil_P.occgtpar = Vue_Fil.typgwvue and TYP_Fil_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'USR_FIL' AS table2, 'Vue_Fil.ucrgwvue = Usr_Fil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_FIL' AS table1, 'VUE_FIL' AS table2, 'Udm_Fil.numgtusr += Vue_Fil.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'GES_FIL' AS table2, 'Vue_Fil.gesgwvue = Ges_Fil.numoeges' AS sql UNION ALL 
	SELECT 'ETA_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ETA_Joi_P.occgtpar = Vue_Joi.etagwvue and ETA_Joi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'GEN_Joi_P.occgtpar = Vue_Joi.gengwvue and GEN_Joi_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'LAN_Joi_P.occgtpar = Vue_Joi.langwvue and LAN_Joi_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_JOI_P' AS table1, 'VUE_JOI' AS table2, 'NAT_Joi_P.occgtpar = Vue_Joi.natgwvue and NAT_Joi_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ROL_Joi_P.occgtpar = Vue_Joi.rolgwvue and ROL_Joi_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_JOI_P' AS table1, 'VUE_JOI' AS table2, 'TYP_Joi_P.occgtpar = Vue_Joi.typgwvue and TYP_Joi_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'USR_JOI' AS table2, 'Vue_Joi.ucrgwvue = Usr_Joi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_JOI' AS table1, 'VUE_JOI' AS table2, 'Udm_Joi.numgtusr += Vue_Joi.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'GES_JOI' AS table2, 'Vue_Joi.gesgwvue = Ges_Joi.numoeges' 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 = 'GWMET' 
	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 + '->%' 
	WHERE NOT EXISTS
	( 
		SELECT * 
		FROM sr1 
		WHERE sr1.table1 = sr2.table2 
		AND sr1.table2 = 'VUE_FIL' 
	) 
) 
SELECT 
	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 = 'VUE_FIL' 
OPTION (MAXRECURSION 0)

Mais elle me retourne :

Code :

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

gwmet.numgwmet = Vue_Fil.metgwvue AND gwmet.langwmet = Vue_Fil.langwvue AND Vue_Fil.tvugwvue = 'F' AND ETA_Fil_P.occgtpar = Vue_Fil.etagwvue AND ETA_Fil_P.padgtpar = 'ETA'

Alors quelle ne devrait me retourner que :

Code :

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

gwmet.numgwmet = Vue_Fil.metgwvue AND gwmet.langwmet = Vue_Fil.langwvue AND Vue_Fil.tvugwvue = 'F'

aieeeuuuuu · 11/08/2011, 16h40

Effectivement, je ne traite pas le cas particulier ou il existe un trajet direct...

au passage, j'ai ajouté dans l'ancrage table2 en plus de table1, et vous aviez visiblement fait un mix de mes deux requêtes pour la gestion des boucles.

Revoici donc une requête qui gère le cas particulier, avec un UNION...
là encore, il y a surement moyen de faire mieux, mais en revoyant la requête récursive...

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
WITH matable AS ( 
	SELECT 'ETA_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ETA_CCol_P.occgtpar = Cla_Col.etagwsql and ETA_CCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'GEN_CCol_P.occgtpar = Cla_Col.gengwsql and GEN_CCol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'LAN_CCol_P.occgtpar = Cla_Col.langwsql and LAN_CCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CCOL_P' AS table1, 'CLA_COL' AS table2, 'NAT_CCol_P.occgtpar = Cla_Col.natgwsql and NAT_CCol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ROL_CCol_P.occgtpar = Cla_Col.rolgwsql and ROL_CCol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_CCOL_P' AS table1, 'CLA_COL' AS table2, 'TYP_CCol_P.occgtpar = Cla_Col.typgwsql and TYP_CCol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'USR_CCOL' AS table2, 'Cla_Col.ucrgwsql = Usr_CCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CCOL' AS table1, 'CLA_COL' AS table2, 'Udm_CCol.numgtusr += Cla_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ETA_ECol_P.occgtpar = Exp_Col.etagwsql and ETA_ECol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'GEN_ECol_P.occgtpar = Exp_Col.gengwsql and GEN_ECol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'LAN_ECol_P.occgtpar = Exp_Col.langwsql and LAN_ECol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_ECOL_P' AS table1, 'EXP_COL' AS table2, 'NAT_ECol_P.occgtpar = Exp_Col.natgwsql and NAT_ECol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ROL_ECol_P.occgtpar = Exp_Col.rolgwsql and ROL_ECol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_ECOL_P' AS table1, 'EXP_COL' AS table2, 'TYP_ECol_P.occgtpar = Exp_Col.typgwsql and TYP_ECol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'USR_ECOL' AS table2, 'Exp_Col.ucrgwsql = Usr_ECol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_ECOL' AS table1, 'EXP_COL' AS table2, 'Udm_ECol.numgtusr += Exp_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ETA_EFil_P.occgtpar = Exp_Fil.etagwsql and ETA_EFil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'GEN_EFil_P.occgtpar = Exp_Fil.gengwsql and GEN_EFil_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'LAN_EFil_P.occgtpar = Exp_Fil.langwsql and LAN_EFil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'NAT_EFil_P.occgtpar = Exp_Fil.natgwsql and NAT_EFil_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ROL_EFil_P.occgtpar = Exp_Fil.rolgwsql and ROL_EFil_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'TYP_EFil_P.occgtpar = Exp_Fil.typgwsql and TYP_EFil_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_FIL' AS table1, 'USR_EFIL' AS table2, 'Exp_Fil.ucrgwsql = Usr_EFil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EFIL' AS table1, 'EXP_FIL' AS table2, 'Udm_EFil.numgtusr += Exp_Fil.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ETA_EJoi_P.occgtpar = Exp_Joi.etagwsql and ETA_EJoi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'GEN_EJoi_P.occgtpar = Exp_Joi.gengwsql and GEN_EJoi_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'NAT_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'NAT_EJoi_P.occgtpar = Exp_Joi.natgwsql and NAT_EJoi_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ROL_EJoi_P.occgtpar = Exp_Joi.rolgwsql and ROL_EJoi_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'TYP_EJoi_P.occgtpar = Exp_Joi.typgwsql and TYP_EJoi_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'USR_EJOI' AS table2, 'Exp_Joi.ucrgwsql = Usr_EJoi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EJOI' AS table1, 'EXP_JOI' AS table2, 'Udm_EJoi.numgtusr += Exp_Joi.udmgwsql' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'VUE_JOI' AS table2, 'Exp_Joi.vuegwsql = Vue_Joi.numgwvue and Exp_Joi.metgwsql = Vue_Joi.metgwvue and Exp_Joi.langwsql = Vue_Joi.langwvue' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'GWCOL' AS table2, 'Cla_Col.metgwsql += gwcol.metgwcol and Cla_Col.vuegwsql += gwcol.vuegwcol and  Cla_Col.langwsql += gwcol.langwcol and Cla_Col.texgwsql += ''C'' and Cla_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'GWCOL' AS table2, 'Exp_Col.metgwsql += gwcol.metgwcol and Exp_Col.vuegwsql += gwcol.vuegwcol and Exp_Col.langwsql += gwcol.langwcol and Exp_Col.texgwsql += ''E'' and Exp_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'AGR_COL_P' AS table1, 'GWCOL' AS table2, 'AGR_Col_P.occgtpar = gwcol.agrgwcol and AGR_Col_P.padgtpar = ''AGRGWCOL''' AS sql UNION ALL 
	SELECT 'ETA_COL_P' AS table1, 'GWCOL' AS table2, 'ETA_Col_P.occgtpar = gwcol.etagwcol and ETA_Col_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_COL_P' AS table1, 'GWCOL' AS table2, 'GEN_Col_P.occgtpar = gwcol.gengwcol and GEN_Col_P.padgtpar = ''GENGWCOL''' AS sql UNION ALL 
	SELECT 'LAN_COL_P' AS table1, 'GWCOL' AS table2, 'LAN_Col_P.occgtpar = gwcol.langwcol and LAN_Col_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_COL_P' AS table1, 'GWCOL' AS table2, 'NAT_Col_P.occgtpar = gwcol.natgwcol and NAT_Col_P.padgtpar = ''NATGWCOL''' AS sql UNION ALL 
	SELECT 'QUA_COL_P' AS table1, 'GWCOL' AS table2, 'QUA_Col_P.occgtpar = gwcol.quagwcol and QUA_Col_P.padgtpar = ''QUAGWCOL''' AS sql UNION ALL 
	SELECT 'ROL_COL_P' AS table1, 'GWCOL' AS table2, 'ROL_Col_P.occgtpar = gwcol.rolgwcol and ROL_Col_P.padgtpar = ''ROLGWCOL''' AS sql UNION ALL 
	SELECT 'TYP_COL_P' AS table1, 'GWCOL' AS table2, 'TYP_Col_P.occgtpar = gwcol.typgwcol and TYP_Col_P.padgtpar = ''TYPGWCOL''' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'USR_COL' AS table2, 'gwcol.ucrgwcol = Usr_Col.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_COL' AS table1, 'GWCOL' AS table2, 'Udm_Col.numgtusr += gwcol.udmgwcol' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'NUI_COL' AS table2, 'gwcol.metgwcol = Nui_Col.metgwcol and gwcol.vuegwcol = Nui_Col.vuegwcol and gwcol.langwcol = Nui_Col.langwcol and gwcol.modgwcol = Nui_Col.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_CTX_P' AS table1, 'GWCTX' AS table2, 'ETA_Ctx_P.occgtpar = GWCTX.etagwctx and ETA_Ctx_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CTX_P' AS table1, 'GWCTX' AS table2, 'GEN_Ctx_P.occgtpar = GWCTX.gengwctx and GEN_Ctx_P.padgtpar = ''GENGWCTX''' AS sql UNION ALL 
	SELECT 'LAN_CTX_P' AS table1, 'GWCTX' AS table2, 'LAN_Ctx_P.occgtpar = GWCTX.langwctx and LAN_Ctx_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CTX_P' AS table1, 'GWCTX' AS table2, 'NAT_Ctx_P.occgtpar = GWCTX.natgwctx and NAT_Ctx_P.padgtpar = ''NATGWCTX''' AS sql UNION ALL 
	SELECT 'ROL_CTX_P' AS table1, 'GWCTX' AS table2, 'ROL_Ctx_P.occgtpar = GWCTX.rolgwctx and ROL_Ctx_P.padgtpar = ''ROLGWCTX''' AS sql UNION ALL 
	SELECT 'TYP_CTX_P' AS table1, 'GWCTX' AS table2, 'TYP_Ctx_P.occgtpar = GWCTX.typgwctx and TYP_Ctx_P.padgtpar = ''TYPGWCTX''' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'USR_CTX' AS table2, 'gwctx.ucrgwctx = Usr_Ctx.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CTX' AS table1, 'GWCTX' AS table2, 'Udm_Ctx.numgtusr += gwctx.udmgwctx' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'GWMET' AS table2, 'gwctx.metgwctx = gwmet.numgwmet and gwctx.langwctx = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'ETA_DEM_P' AS table1, 'GWDEM' AS table2, 'ETA_Dem_P.occgtpar = gwdem.etagwdem and ETA_Dem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_DEM_P' AS table1, 'GWDEM' AS table2, 'GEN_Dem_P.occgtpar = gwdem.gengwdem and GEN_Dem_P.padgtpar = ''GENGWDEM''' AS sql UNION ALL 
	SELECT 'LAN_DEM_P' AS table1, 'GWDEM' AS table2, 'LAN_Dem_P.occgtpar = gwdem.langwdem and Lan_Dem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_DEM_P' AS table1, 'GWDEM' AS table2, 'NAT_Dem_P.occgtpar = gwdem.natgwdem and NAT_Dem_P.padgtpar = ''NATGWDEM''' AS sql UNION ALL 
	SELECT 'ROL_DEM_P' AS table1, 'GWDEM' AS table2, 'ROL_Dem_P.occgtpar = gwdem.rolgwdem and ROL_Dem_P.padgtpar = ''ROLGWDEM''' AS sql UNION ALL 
	SELECT 'TVA_DEM_P' AS table1, 'GWDEM' AS table2, 'TVA_Dem_P.occgtpar = gwdem.tvagwdem and TVA_Dem_P.padgtpar = ''TVAGWDEM''' AS sql UNION ALL 
	SELECT 'TYP_DEM_P' AS table1, 'GWDEM' AS table2, 'TYP_Dem_P.occgtpar = gwdem.typgwdem and TYP_Dem_P.padgtpar = ''TYPGWDEM''' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'USR_DEM' AS table2, 'gwdem.ucrgwdem = Usr_Dem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_DEM' AS table1, 'GWDEM' AS table2, 'Udm_Dem.numgtusr += gwdem.udmgwdem' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'VUE_DEM' AS table2, 'gwdem.vuegwdem = Vue_Dem.numgwvue and gwdem.metgwdem = Vue_Dem.metgwvue and gwdem.langwdem = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'COL_HIE' AS table2, 'gwhie.metgwhie = Col_Hie.metgwcol and gwhie.langwhie = Col_Hie.langwcol and gwhie.ncogwhie = Col_Hie.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_HIE_P' AS table1, 'GWHIE' AS table2, 'ETA_Hie_P.occgtpar = gwhie.etagwhie and ETA_Hie_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_HIE_P' AS table1, 'GWHIE' AS table2, 'GEN_Hie_P.occgtpar = gwhie.gengwhie and GEN_Hie_P.padgtpar = ''GENGWHIE''' AS sql UNION ALL 
	SELECT 'LAN_HIE_P' AS table1, 'GWHIE' AS table2, 'LAN_Hie_P.occgtpar = gwhie.langwhie and LAN_Hie_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_HIE_P' AS table1, 'GWHIE' AS table2, 'NAT_Hie_P.occgtpar = gwhie.natgwhie and NAT_Hie_P.padgtpar = ''NATGWHIE''' AS sql UNION ALL 
	SELECT 'ROL_HIE_P' AS table1, 'GWHIE' AS table2, 'ROL_Hie_P.occgtpar = gwhie.rolgwhie and ROL_Hie_P.padgtpar = ''ROLGWHIE''' AS sql UNION ALL 
	SELECT 'TYP_HIE_P' AS table1, 'GWHIE' AS table2, 'TYP_Hie_P.occgtpar = gwhie.typgwhie and TYP_Hie_P.padgtpar = ''TYPGWHIE''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'USR_HIE' AS table2, 'gwhie.ucrgwhie = Usr_Hie.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_HIE' AS table1, 'GWHIE' AS table2, 'Udm_Hie.numgtusr += gwhie.udmgwhie' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'GWMET' AS table2, 'gwhie.metgwhie = gwmet.numgwmet and gwhie.langwhie = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GTAPP' AS table2, 'gwmet.appgwmet = gtapp.numgtapp' AS sql UNION ALL 
	SELECT 'ETA_MET_P' AS table1, 'GWMET' AS table2, 'ETA_Met_P.occgtpar = gwmet.etagwmet and ETA_Met_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_MET_P' AS table1, 'GWMET' AS table2, 'GEN_Met_P.occgtpar = gwmet.gengwmet and GEN_Met_P.padgtpar = ''GENGWMET''' AS sql UNION ALL 
	SELECT 'LAN_MET_P' AS table1, 'GWMET' AS table2, 'LAN_Met_P.occgtpar = gwmet.langwmet and LAN_Met_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_MET_P' AS table1, 'GWMET' AS table2, 'NAT_Met_P.occgtpar = gwmet.natgwmet and NAT_Met_P.padgtpar = ''NATGWMET''' AS sql UNION ALL 
	SELECT 'ROL_MET_P' AS table1, 'GWMET' AS table2, 'ROL_Met_P.occgtpar = gwmet.rolgwmet and ROL_Met_P.padgtpar = ''ROLGWMET''' AS sql UNION ALL 
	SELECT 'TYP_MET_P' AS table1, 'GWMET' AS table2, 'TYP_Met_P.occgtpar = gwmet.typgwmet and TYP_Met_P.padgtpar = ''TYPGWMET''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'USR_MET' AS table2, 'gwmet.ucrgwmet = Usr_Met.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_MET' AS table1, 'GWMET' AS table2, 'Udm_Met.numgtusr += gwmet.udmgwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GES_MET' AS table2, 'gwmet.gesgwmet = Ges_Met.numoeges' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_COL' AS table2, 'gwmet.numgwmet = Vue_Col.metgwvue and gwmet.langwmet = Vue_Col.langwvue and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_DEM' AS table2, 'gwmet.numgwmet = Vue_Dem.metgwvue and gwmet.langwmet = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_FIL' AS table2, 'gwmet.numgwmet = Vue_Fil.metgwvue and gwmet.langwmet = Vue_Fil.langwvue and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_JOI' AS table2, 'gwmet.numgwmet = Vue_Joi.metgwvue and gwmet.langwmet = Vue_Joi.langwvue and Vue_Joi.tvugwvue = ''J''' AS sql UNION ALL 
	SELECT 'ETA_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ETA_VCol_P.occgtpar = Vue_Col.etagwvue and ETA_VCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'GEN_VCol_P.occgtpar = Vue_Col.gengwvue and GEN_VCol_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'LAN_VCol_P.occgtpar = Vue_Col.langwvue and LAN_VCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VCOL_P' AS table1, 'VUE_COL' AS table2, 'NAT_VCol_P.occgtpar = Vue_Col.natgwvue and NAT_VCol_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ROL_VCol_P.occgtpar = Vue_Col.rolgwvue and ROL_VCol_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VCOL_P' AS table1, 'VUE_COL' AS table2, 'TYP_VCol_P.occgtpar = Vue_Col.typgwvue and TYP_VCol_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'USR_VCOL' AS table2, 'Vue_Col.ucrgwvue = Usr_VCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VCOL' AS table1, 'VUE_COL' AS table2, 'Udm_VCol.numgtusr += Vue_Col.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GWCOL' AS table2, 'Vue_Col.metgwvue = gwcol.metgwcol and Vue_Col.langwvue = gwcol.langwcol and Vue_Col.numgwvue = gwcol.vuegwcol and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GES_VCOL' AS table2, 'Vue_Col.gesgwvue = Ges_VCol.numoeges' AS sql UNION ALL 
	SELECT 'ETA_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ETA_VDem_P.occgtpar = Vue_Dem.etagwvue and ETA_VDem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'GEN_VDem_P.occgtpar = Vue_Dem.gengwvue and GEN_VDem_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'LAN_VDem_P.occgtpar = Vue_Dem.langwvue and LAN_VDem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'NAT_VDem_P.occgtpar = Vue_Dem.natgwvue and NAT_VDem_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ROL_VDem_P.occgtpar = Vue_Dem.rolgwvue and ROL_VDem_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'TYP_VDem_P.occgtpar = Vue_Dem.typgwvue and TYP_VDem_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'USR_VDEM' AS table2, 'Vue_Dem.ucrgwvue = Usr_VDem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VDEM' AS table1, 'VUE_DEM' AS table2, 'Udm_VDem.numgtusr += Vue_Dem.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'GES_VDEM' AS table2, 'Vue_Dem.gesgwvue = Ges_VDem.numoeges' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'EXP_FIL' AS table2, 'Vue_Fil.metgwvue = Exp_Fil.metgwsql and Vue_Fil.langwvue = Exp_Fil.langwsql and Vue_Fil.numgwvue = Exp_Fil.vuegwsql and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'ETA_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ETA_Fil_P.occgtpar = Vue_Fil.etagwvue and ETA_Fil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'GEN_Fil_P.occgtpar = Vue_Fil.gengwvue and GEN_Fil_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'LAN_Fil_P.occgtpar = Vue_Fil.langwvue and LAN_Fil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_FIL_P' AS table1, 'VUE_FIL' AS table2, 'NAT_Fil_P.occgtpar = Vue_Fil.natgwvue and NAT_Fil_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ROL_Fil_P.occgtpar = Vue_Fil.rolgwvue and ROL_Fil_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_FIL_P' AS table1, 'VUE_FIL' AS table2, 'TYP_Fil_P.occgtpar = Vue_Fil.typgwvue and TYP_Fil_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'USR_FIL' AS table2, 'Vue_Fil.ucrgwvue = Usr_Fil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_FIL' AS table1, 'VUE_FIL' AS table2, 'Udm_Fil.numgtusr += Vue_Fil.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'GES_FIL' AS table2, 'Vue_Fil.gesgwvue = Ges_Fil.numoeges' AS sql UNION ALL 
	SELECT 'ETA_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ETA_Joi_P.occgtpar = Vue_Joi.etagwvue and ETA_Joi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'GEN_Joi_P.occgtpar = Vue_Joi.gengwvue and GEN_Joi_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'LAN_Joi_P.occgtpar = Vue_Joi.langwvue and LAN_Joi_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_JOI_P' AS table1, 'VUE_JOI' AS table2, 'NAT_Joi_P.occgtpar = Vue_Joi.natgwvue and NAT_Joi_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ROL_Joi_P.occgtpar = Vue_Joi.rolgwvue and ROL_Joi_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_JOI_P' AS table1, 'VUE_JOI' AS table2, 'TYP_Joi_P.occgtpar = Vue_Joi.typgwvue and TYP_Joi_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'USR_JOI' AS table2, 'Vue_Joi.ucrgwvue = Usr_Joi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_JOI' AS table1, 'VUE_JOI' AS table2, 'Udm_Joi.numgtusr += Vue_Joi.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'GES_JOI' AS table2, 'Vue_Joi.gesgwvue = Ges_Joi.numoeges' 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)) +'->' + CAST(table2 AS VARCHAR(MAX)) + '->' AS rte
	FROM sr1
	WHERE  table1 = 'GWMET'
	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.table2 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 =  'VUE_FIL'  
		)
		AND NOT EXISTS(
			SELECT *
			FROM sr1
			WHERE table1 = 'GWMET' 
				AND table2 = 'VUE_FIL'  
			)
	) 
	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 = 'VUE_FIL'  
	UNION ALL
	SELECT 
		CAST(table1 AS VARCHAR(MAX)) +'->' + CAST(table2 AS VARCHAR(MAX)) + '->' AS rte,
		CAST(1 AS INT) AS L,
		table1,
		table2,
		CAST(sr1.sql AS VARCHAR(MAX)) AS chemin
	FROM sr1
	WHERE  table1 = 'GWMET'
		AND table2 = 'VUE_FIL'  
OPTION (MAXRECURSION 0)

aieeeuuuuu · 11/08/2011, 17h11

hmmm

en fait, plus je la relis, et moins je la trouve correcte....

disons qu'elle doit plus ou moins fonctionner....

J'y rejette un oeil...

ghosty177 · 11/08/2011, 17h14

Ok ca a l'air good maintenant, donc merci beaucoup.

je verrais pour l'optimisation plus tard (après les vac

).

merci aieeeuuuuu.

aieeeuuuuu · 11/08/2011, 17h32

hmmm

c'est à vérifier, mais je pense qu'elle pourrait sortir tous les chemin possible en fait...

Une autre requête plus académique :
1/ CTE qui cherche tous les chemins possibles à partir du point de départ, sans repasser plusieurs fois par le même point
2/ recherche du chemin le plus court parmi ceux qui mènent "à bon port" dans la requête finale.

Contrairement à ce que je disais, je pense que mes requêtes précédentes calculaient bien tous les chemins possibles

bref, testez celle-ci, je pense qu'elle est moins tordue :

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
WITH matable AS ( 
	SELECT 'ETA_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ETA_CCol_P.occgtpar = Cla_Col.etagwsql and ETA_CCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'GEN_CCol_P.occgtpar = Cla_Col.gengwsql and GEN_CCol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_CCOL_P' AS table1, 'CLA_COL' AS table2, 'LAN_CCol_P.occgtpar = Cla_Col.langwsql and LAN_CCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CCOL_P' AS table1, 'CLA_COL' AS table2, 'NAT_CCol_P.occgtpar = Cla_Col.natgwsql and NAT_CCol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_CCOL_P' AS table1, 'CLA_COL' AS table2, 'ROL_CCol_P.occgtpar = Cla_Col.rolgwsql and ROL_CCol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_CCOL_P' AS table1, 'CLA_COL' AS table2, 'TYP_CCol_P.occgtpar = Cla_Col.typgwsql and TYP_CCol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'USR_CCOL' AS table2, 'Cla_Col.ucrgwsql = Usr_CCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CCOL' AS table1, 'CLA_COL' AS table2, 'Udm_CCol.numgtusr += Cla_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ETA_ECol_P.occgtpar = Exp_Col.etagwsql and ETA_ECol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'GEN_ECol_P.occgtpar = Exp_Col.gengwsql and GEN_ECol_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_ECOL_P' AS table1, 'EXP_COL' AS table2, 'LAN_ECol_P.occgtpar = Exp_Col.langwsql and LAN_ECol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_ECOL_P' AS table1, 'EXP_COL' AS table2, 'NAT_ECol_P.occgtpar = Exp_Col.natgwsql and NAT_ECol_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_ECOL_P' AS table1, 'EXP_COL' AS table2, 'ROL_ECol_P.occgtpar = Exp_Col.rolgwsql and ROL_ECol_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_ECOL_P' AS table1, 'EXP_COL' AS table2, 'TYP_ECol_P.occgtpar = Exp_Col.typgwsql and TYP_ECol_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'USR_ECOL' AS table2, 'Exp_Col.ucrgwsql = Usr_ECol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_ECOL' AS table1, 'EXP_COL' AS table2, 'Udm_ECol.numgtusr += Exp_Col.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ETA_EFil_P.occgtpar = Exp_Fil.etagwsql and ETA_EFil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'GEN_EFil_P.occgtpar = Exp_Fil.gengwsql and GEN_EFil_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'LAN_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'LAN_EFil_P.occgtpar = Exp_Fil.langwsql and LAN_EFil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'NAT_EFil_P.occgtpar = Exp_Fil.natgwsql and NAT_EFil_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'ROL_EFil_P.occgtpar = Exp_Fil.rolgwsql and ROL_EFil_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EFIL_P' AS table1, 'EXP_FIL' AS table2, 'TYP_EFil_P.occgtpar = Exp_Fil.typgwsql and TYP_EFil_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_FIL' AS table1, 'USR_EFIL' AS table2, 'Exp_Fil.ucrgwsql = Usr_EFil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EFIL' AS table1, 'EXP_FIL' AS table2, 'Udm_EFil.numgtusr += Exp_Fil.udmgwsql' AS sql UNION ALL 
	SELECT 'ETA_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ETA_EJoi_P.occgtpar = Exp_Joi.etagwsql and ETA_EJoi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'GEN_EJoi_P.occgtpar = Exp_Joi.gengwsql and GEN_EJoi_P.padgtpar = ''GENGWSQL''' AS sql UNION ALL 
	SELECT 'NAT_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'NAT_EJoi_P.occgtpar = Exp_Joi.natgwsql and NAT_EJoi_P.padgtpar = ''NATGWSQL''' AS sql UNION ALL 
	SELECT 'ROL_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'ROL_EJoi_P.occgtpar = Exp_Joi.rolgwsql and ROL_EJoi_P.padgtpar = ''ROLGWSQL''' AS sql UNION ALL 
	SELECT 'TYP_EJOI_P' AS table1, 'EXP_JOI' AS table2, 'TYP_EJoi_P.occgtpar = Exp_Joi.typgwsql and TYP_EJoi_P.padgtpar = ''TYPGWSQL''' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'USR_EJOI' AS table2, 'Exp_Joi.ucrgwsql = Usr_EJoi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_EJOI' AS table1, 'EXP_JOI' AS table2, 'Udm_EJoi.numgtusr += Exp_Joi.udmgwsql' AS sql UNION ALL 
	SELECT 'EXP_JOI' AS table1, 'VUE_JOI' AS table2, 'Exp_Joi.vuegwsql = Vue_Joi.numgwvue and Exp_Joi.metgwsql = Vue_Joi.metgwvue and Exp_Joi.langwsql = Vue_Joi.langwvue' AS sql UNION ALL 
	SELECT 'CLA_COL' AS table1, 'GWCOL' AS table2, 'Cla_Col.metgwsql += gwcol.metgwcol and Cla_Col.vuegwsql += gwcol.vuegwcol and  Cla_Col.langwsql += gwcol.langwcol and Cla_Col.texgwsql += ''C'' and Cla_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'EXP_COL' AS table1, 'GWCOL' AS table2, 'Exp_Col.metgwsql += gwcol.metgwcol and Exp_Col.vuegwsql += gwcol.vuegwcol and Exp_Col.langwsql += gwcol.langwcol and Exp_Col.texgwsql += ''E'' and Exp_Col.colgwsql += gwcol.colgwcol' AS sql UNION ALL 
	SELECT 'AGR_COL_P' AS table1, 'GWCOL' AS table2, 'AGR_Col_P.occgtpar = gwcol.agrgwcol and AGR_Col_P.padgtpar = ''AGRGWCOL''' AS sql UNION ALL 
	SELECT 'ETA_COL_P' AS table1, 'GWCOL' AS table2, 'ETA_Col_P.occgtpar = gwcol.etagwcol and ETA_Col_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_COL_P' AS table1, 'GWCOL' AS table2, 'GEN_Col_P.occgtpar = gwcol.gengwcol and GEN_Col_P.padgtpar = ''GENGWCOL''' AS sql UNION ALL 
	SELECT 'LAN_COL_P' AS table1, 'GWCOL' AS table2, 'LAN_Col_P.occgtpar = gwcol.langwcol and LAN_Col_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_COL_P' AS table1, 'GWCOL' AS table2, 'NAT_Col_P.occgtpar = gwcol.natgwcol and NAT_Col_P.padgtpar = ''NATGWCOL''' AS sql UNION ALL 
	SELECT 'QUA_COL_P' AS table1, 'GWCOL' AS table2, 'QUA_Col_P.occgtpar = gwcol.quagwcol and QUA_Col_P.padgtpar = ''QUAGWCOL''' AS sql UNION ALL 
	SELECT 'ROL_COL_P' AS table1, 'GWCOL' AS table2, 'ROL_Col_P.occgtpar = gwcol.rolgwcol and ROL_Col_P.padgtpar = ''ROLGWCOL''' AS sql UNION ALL 
	SELECT 'TYP_COL_P' AS table1, 'GWCOL' AS table2, 'TYP_Col_P.occgtpar = gwcol.typgwcol and TYP_Col_P.padgtpar = ''TYPGWCOL''' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'USR_COL' AS table2, 'gwcol.ucrgwcol = Usr_Col.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_COL' AS table1, 'GWCOL' AS table2, 'Udm_Col.numgtusr += gwcol.udmgwcol' AS sql UNION ALL 
	SELECT 'GWCOL' AS table1, 'NUI_COL' AS table2, 'gwcol.metgwcol = Nui_Col.metgwcol and gwcol.vuegwcol = Nui_Col.vuegwcol and gwcol.langwcol = Nui_Col.langwcol and gwcol.modgwcol = Nui_Col.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_CTX_P' AS table1, 'GWCTX' AS table2, 'ETA_Ctx_P.occgtpar = GWCTX.etagwctx and ETA_Ctx_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_CTX_P' AS table1, 'GWCTX' AS table2, 'GEN_Ctx_P.occgtpar = GWCTX.gengwctx and GEN_Ctx_P.padgtpar = ''GENGWCTX''' AS sql UNION ALL 
	SELECT 'LAN_CTX_P' AS table1, 'GWCTX' AS table2, 'LAN_Ctx_P.occgtpar = GWCTX.langwctx and LAN_Ctx_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_CTX_P' AS table1, 'GWCTX' AS table2, 'NAT_Ctx_P.occgtpar = GWCTX.natgwctx and NAT_Ctx_P.padgtpar = ''NATGWCTX''' AS sql UNION ALL 
	SELECT 'ROL_CTX_P' AS table1, 'GWCTX' AS table2, 'ROL_Ctx_P.occgtpar = GWCTX.rolgwctx and ROL_Ctx_P.padgtpar = ''ROLGWCTX''' AS sql UNION ALL 
	SELECT 'TYP_CTX_P' AS table1, 'GWCTX' AS table2, 'TYP_Ctx_P.occgtpar = GWCTX.typgwctx and TYP_Ctx_P.padgtpar = ''TYPGWCTX''' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'USR_CTX' AS table2, 'gwctx.ucrgwctx = Usr_Ctx.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_CTX' AS table1, 'GWCTX' AS table2, 'Udm_Ctx.numgtusr += gwctx.udmgwctx' AS sql UNION ALL 
	SELECT 'GWCTX' AS table1, 'GWMET' AS table2, 'gwctx.metgwctx = gwmet.numgwmet and gwctx.langwctx = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'ETA_DEM_P' AS table1, 'GWDEM' AS table2, 'ETA_Dem_P.occgtpar = gwdem.etagwdem and ETA_Dem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_DEM_P' AS table1, 'GWDEM' AS table2, 'GEN_Dem_P.occgtpar = gwdem.gengwdem and GEN_Dem_P.padgtpar = ''GENGWDEM''' AS sql UNION ALL 
	SELECT 'LAN_DEM_P' AS table1, 'GWDEM' AS table2, 'LAN_Dem_P.occgtpar = gwdem.langwdem and Lan_Dem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_DEM_P' AS table1, 'GWDEM' AS table2, 'NAT_Dem_P.occgtpar = gwdem.natgwdem and NAT_Dem_P.padgtpar = ''NATGWDEM''' AS sql UNION ALL 
	SELECT 'ROL_DEM_P' AS table1, 'GWDEM' AS table2, 'ROL_Dem_P.occgtpar = gwdem.rolgwdem and ROL_Dem_P.padgtpar = ''ROLGWDEM''' AS sql UNION ALL 
	SELECT 'TVA_DEM_P' AS table1, 'GWDEM' AS table2, 'TVA_Dem_P.occgtpar = gwdem.tvagwdem and TVA_Dem_P.padgtpar = ''TVAGWDEM''' AS sql UNION ALL 
	SELECT 'TYP_DEM_P' AS table1, 'GWDEM' AS table2, 'TYP_Dem_P.occgtpar = gwdem.typgwdem and TYP_Dem_P.padgtpar = ''TYPGWDEM''' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'USR_DEM' AS table2, 'gwdem.ucrgwdem = Usr_Dem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_DEM' AS table1, 'GWDEM' AS table2, 'Udm_Dem.numgtusr += gwdem.udmgwdem' AS sql UNION ALL 
	SELECT 'GWDEM' AS table1, 'VUE_DEM' AS table2, 'gwdem.vuegwdem = Vue_Dem.numgwvue and gwdem.metgwdem = Vue_Dem.metgwvue and gwdem.langwdem = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'COL_HIE' AS table2, 'gwhie.metgwhie = Col_Hie.metgwcol and gwhie.langwhie = Col_Hie.langwcol and gwhie.ncogwhie = Col_Hie.nuigwcol' AS sql UNION ALL 
	SELECT 'ETA_HIE_P' AS table1, 'GWHIE' AS table2, 'ETA_Hie_P.occgtpar = gwhie.etagwhie and ETA_Hie_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_HIE_P' AS table1, 'GWHIE' AS table2, 'GEN_Hie_P.occgtpar = gwhie.gengwhie and GEN_Hie_P.padgtpar = ''GENGWHIE''' AS sql UNION ALL 
	SELECT 'LAN_HIE_P' AS table1, 'GWHIE' AS table2, 'LAN_Hie_P.occgtpar = gwhie.langwhie and LAN_Hie_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_HIE_P' AS table1, 'GWHIE' AS table2, 'NAT_Hie_P.occgtpar = gwhie.natgwhie and NAT_Hie_P.padgtpar = ''NATGWHIE''' AS sql UNION ALL 
	SELECT 'ROL_HIE_P' AS table1, 'GWHIE' AS table2, 'ROL_Hie_P.occgtpar = gwhie.rolgwhie and ROL_Hie_P.padgtpar = ''ROLGWHIE''' AS sql UNION ALL 
	SELECT 'TYP_HIE_P' AS table1, 'GWHIE' AS table2, 'TYP_Hie_P.occgtpar = gwhie.typgwhie and TYP_Hie_P.padgtpar = ''TYPGWHIE''' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'USR_HIE' AS table2, 'gwhie.ucrgwhie = Usr_Hie.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_HIE' AS table1, 'GWHIE' AS table2, 'Udm_Hie.numgtusr += gwhie.udmgwhie' AS sql UNION ALL 
	SELECT 'GWHIE' AS table1, 'GWMET' AS table2, 'gwhie.metgwhie = gwmet.numgwmet and gwhie.langwhie = gwmet.langwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GTAPP' AS table2, 'gwmet.appgwmet = gtapp.numgtapp' AS sql UNION ALL 
	SELECT 'ETA_MET_P' AS table1, 'GWMET' AS table2, 'ETA_Met_P.occgtpar = gwmet.etagwmet and ETA_Met_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_MET_P' AS table1, 'GWMET' AS table2, 'GEN_Met_P.occgtpar = gwmet.gengwmet and GEN_Met_P.padgtpar = ''GENGWMET''' AS sql UNION ALL 
	SELECT 'LAN_MET_P' AS table1, 'GWMET' AS table2, 'LAN_Met_P.occgtpar = gwmet.langwmet and LAN_Met_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_MET_P' AS table1, 'GWMET' AS table2, 'NAT_Met_P.occgtpar = gwmet.natgwmet and NAT_Met_P.padgtpar = ''NATGWMET''' AS sql UNION ALL 
	SELECT 'ROL_MET_P' AS table1, 'GWMET' AS table2, 'ROL_Met_P.occgtpar = gwmet.rolgwmet and ROL_Met_P.padgtpar = ''ROLGWMET''' AS sql UNION ALL 
	SELECT 'TYP_MET_P' AS table1, 'GWMET' AS table2, 'TYP_Met_P.occgtpar = gwmet.typgwmet and TYP_Met_P.padgtpar = ''TYPGWMET''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'USR_MET' AS table2, 'gwmet.ucrgwmet = Usr_Met.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_MET' AS table1, 'GWMET' AS table2, 'Udm_Met.numgtusr += gwmet.udmgwmet' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'GES_MET' AS table2, 'gwmet.gesgwmet = Ges_Met.numoeges' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_COL' AS table2, 'gwmet.numgwmet = Vue_Col.metgwvue and gwmet.langwmet = Vue_Col.langwvue and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_DEM' AS table2, 'gwmet.numgwmet = Vue_Dem.metgwvue and gwmet.langwmet = Vue_Dem.langwvue and Vue_Dem.tvugwvue = ''D''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_FIL' AS table2, 'gwmet.numgwmet = Vue_Fil.metgwvue and gwmet.langwmet = Vue_Fil.langwvue and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'GWMET' AS table1, 'VUE_JOI' AS table2, 'gwmet.numgwmet = Vue_Joi.metgwvue and gwmet.langwmet = Vue_Joi.langwvue and Vue_Joi.tvugwvue = ''J''' AS sql UNION ALL 
	SELECT 'ETA_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ETA_VCol_P.occgtpar = Vue_Col.etagwvue and ETA_VCol_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'GEN_VCol_P.occgtpar = Vue_Col.gengwvue and GEN_VCol_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VCOL_P' AS table1, 'VUE_COL' AS table2, 'LAN_VCol_P.occgtpar = Vue_Col.langwvue and LAN_VCol_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VCOL_P' AS table1, 'VUE_COL' AS table2, 'NAT_VCol_P.occgtpar = Vue_Col.natgwvue and NAT_VCol_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VCOL_P' AS table1, 'VUE_COL' AS table2, 'ROL_VCol_P.occgtpar = Vue_Col.rolgwvue and ROL_VCol_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VCOL_P' AS table1, 'VUE_COL' AS table2, 'TYP_VCol_P.occgtpar = Vue_Col.typgwvue and TYP_VCol_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'USR_VCOL' AS table2, 'Vue_Col.ucrgwvue = Usr_VCol.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VCOL' AS table1, 'VUE_COL' AS table2, 'Udm_VCol.numgtusr += Vue_Col.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GWCOL' AS table2, 'Vue_Col.metgwvue = gwcol.metgwcol and Vue_Col.langwvue = gwcol.langwcol and Vue_Col.numgwvue = gwcol.vuegwcol and Vue_Col.tvugwvue = ''C''' AS sql UNION ALL 
	SELECT 'VUE_COL' AS table1, 'GES_VCOL' AS table2, 'Vue_Col.gesgwvue = Ges_VCol.numoeges' AS sql UNION ALL 
	SELECT 'ETA_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ETA_VDem_P.occgtpar = Vue_Dem.etagwvue and ETA_VDem_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'GEN_VDem_P.occgtpar = Vue_Dem.gengwvue and GEN_VDem_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'LAN_VDem_P.occgtpar = Vue_Dem.langwvue and LAN_VDem_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'NAT_VDem_P.occgtpar = Vue_Dem.natgwvue and NAT_VDem_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'ROL_VDem_P.occgtpar = Vue_Dem.rolgwvue and ROL_VDem_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_VDEM_P' AS table1, 'VUE_DEM' AS table2, 'TYP_VDem_P.occgtpar = Vue_Dem.typgwvue and TYP_VDem_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'USR_VDEM' AS table2, 'Vue_Dem.ucrgwvue = Usr_VDem.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_VDEM' AS table1, 'VUE_DEM' AS table2, 'Udm_VDem.numgtusr += Vue_Dem.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_DEM' AS table1, 'GES_VDEM' AS table2, 'Vue_Dem.gesgwvue = Ges_VDem.numoeges' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'EXP_FIL' AS table2, 'Vue_Fil.metgwvue = Exp_Fil.metgwsql and Vue_Fil.langwvue = Exp_Fil.langwsql and Vue_Fil.numgwvue = Exp_Fil.vuegwsql and Vue_Fil.tvugwvue = ''F''' AS sql UNION ALL 
	SELECT 'ETA_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ETA_Fil_P.occgtpar = Vue_Fil.etagwvue and ETA_Fil_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'GEN_Fil_P.occgtpar = Vue_Fil.gengwvue and GEN_Fil_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_FIL_P' AS table1, 'VUE_FIL' AS table2, 'LAN_Fil_P.occgtpar = Vue_Fil.langwvue and LAN_Fil_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_FIL_P' AS table1, 'VUE_FIL' AS table2, 'NAT_Fil_P.occgtpar = Vue_Fil.natgwvue and NAT_Fil_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_FIL_P' AS table1, 'VUE_FIL' AS table2, 'ROL_Fil_P.occgtpar = Vue_Fil.rolgwvue and ROL_Fil_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_FIL_P' AS table1, 'VUE_FIL' AS table2, 'TYP_Fil_P.occgtpar = Vue_Fil.typgwvue and TYP_Fil_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'USR_FIL' AS table2, 'Vue_Fil.ucrgwvue = Usr_Fil.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_FIL' AS table1, 'VUE_FIL' AS table2, 'Udm_Fil.numgtusr += Vue_Fil.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_FIL' AS table1, 'GES_FIL' AS table2, 'Vue_Fil.gesgwvue = Ges_Fil.numoeges' AS sql UNION ALL 
	SELECT 'ETA_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ETA_Joi_P.occgtpar = Vue_Joi.etagwvue and ETA_Joi_P.padgtpar = ''ETA''' AS sql UNION ALL 
	SELECT 'GEN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'GEN_Joi_P.occgtpar = Vue_Joi.gengwvue and GEN_Joi_P.padgtpar = ''GENGWVUE''' AS sql UNION ALL 
	SELECT 'LAN_JOI_P' AS table1, 'VUE_JOI' AS table2, 'LAN_Joi_P.occgtpar = Vue_Joi.langwvue and LAN_Joi_P.padgtpar = ''LANGUE''' AS sql UNION ALL 
	SELECT 'NAT_JOI_P' AS table1, 'VUE_JOI' AS table2, 'NAT_Joi_P.occgtpar = Vue_Joi.natgwvue and NAT_Joi_P.padgtpar = ''NATGWVUE''' AS sql UNION ALL 
	SELECT 'ROL_JOI_P' AS table1, 'VUE_JOI' AS table2, 'ROL_Joi_P.occgtpar = Vue_Joi.rolgwvue and ROL_Joi_P.padgtpar = ''ROLGWVUE''' AS sql UNION ALL 
	SELECT 'TYP_JOI_P' AS table1, 'VUE_JOI' AS table2, 'TYP_Joi_P.occgtpar = Vue_Joi.typgwvue and TYP_Joi_P.padgtpar = ''TYPGWVUE''' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'USR_JOI' AS table2, 'Vue_Joi.ucrgwvue = Usr_Joi.numgtusr' AS sql UNION ALL 
	SELECT 'UDM_JOI' AS table1, 'VUE_JOI' AS table2, 'Udm_Joi.numgtusr += Vue_Joi.udmgwvue' AS sql UNION ALL 
	SELECT 'VUE_JOI' AS table1, 'GES_JOI' AS table2, 'Vue_Joi.gesgwvue = Ges_Joi.numoeges' 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)) +'->' + CAST(table2 AS VARCHAR(MAX)) + '->' AS rte
	FROM sr1
	WHERE  table1 = 'GWMET'
	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.table2 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
 
	) 
	SELECT 
		sr2.L + 1 AS niveau,
		CAST(sr2.chemin AS VARCHAR(MAX)) --+ CAST(' AND ' AS VARCHAR(5)) + CAST(sr1.sql AS VARCHAR(MAX)) AS chemin
	FROM sr2
	WHERE table2 = 'VUE_FIL'  
		AND L = (
			SELECT MIN(L) 
			FROM sr2 
			WHERE table2 = 'VUE_FIL'  
		)
OPTION (MAXRECURSION 0)

aieeeuuuuu · 12/08/2011, 09h56

C'est encore moi

Je pense finalement avoir trouvé une solution pour arrêter la récursion des qu'on a trouvé un chemin...
mais au prix d'une fonction de fenêtrage...

à comparer donc sur votre jeu de données complet par rapport à la requête précédente (il ne vous reste qu'a annuler vos vacances

)

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
 
sr2 AS (
	SELECT 
		MAX(CASE WHEN sr1.table2 = 'VUE_FIL' THEN 1 ELSE 0 END) OVER() AS fin,
		CAST(1 AS INT) AS L,
		table1,
		table2,
		CAST(sr1.sql AS VARCHAR(MAX)) AS chemin,
		CAST(table1 AS VARCHAR(MAX)) +'->' + CAST(table2 AS VARCHAR(MAX)) + '->' AS rte
	FROM sr1
	WHERE  table1 = 'GWMET'
	UNION ALL
	SELECT 
		MAX(CASE WHEN sr1.table2 = 'VUE_FIL' THEN 1 ELSE 0 END) OVER(),
		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.table2 AS VARCHAR(MAX)) +'->' 
	FROM sr2
	INNER JOIN  sr1 
		ON sr2.table2 = sr1.table1
		AND sr2.rte NOT LIKE '%' + sr1.table2 + '->%'-- => pour eviter les boucles infinie
	WHERE sr2.fin = 0
 
	) 
	SELECT 
		sr2.L + 1 AS niveau,
		CAST(sr2.chemin AS VARCHAR(MAX)) 
	FROM sr2
	WHERE table2 = 'VUE_FIL'  
OPTION (MAXRECURSION 0)

ghosty177 · 23/08/2011, 14h58

Salut aieeeuuuuu,
Bon finalement je n'ai pas annulé mes vacances donc me revoilà.

Je viens d'effectuer quelques test et ta dernière requête semble me retourner les résultats plus rapidement et avec un seul résultat à chaque fois.
Du coup je pense rester sur celle la.

Je te remercie pour ton aide.
Bonne continuation.
Ghosty

10/08/2011, 10h13	#2
elsuket Modérateur Nicolas Souquet Administrateur de base de données Inscription : janvier 2005 Messages : 4 669 Détails du profil Informations personnelles : Nom : Nicolas Souquet Âge : 30 Localisation : Thaïlande Informations professionnelles : Activité : Administrateur de base de données Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : janvier 2005 Messages : 4 669 Points : 8 729 Points : 8 729	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 ? @++ __________________ En bases de données relationnelles SQL, il n'y a ni tableaux, ni enregistrements, ni champs: il y a des tables, des lignes et des colonnes. Blog \| Profil\| Consulter ou télécharger les fichiers d'aide de SQL Server, des versions 2000 à 2012
	00

10/08/2011, 11h19	#3
ghosty177 Futur Membre du Club Inscription : juin 2007 Messages : 69 Détails du profil Informations forums : Inscription : juin 2007 Messages : 69 Points : 15 Points : 15	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.
	00

11/08/2011, 17h11	#9
aieeeuuuuu Membre Expert Inscription : janvier 2010 Messages : 1 084 Détails du profil Informations personnelles : Localisation : France, Rhône (Rhône Alpes) Informations forums : Inscription : janvier 2010 Messages : 1 084 Points : 1 573 Points : 1 573	hmmm en fait, plus je la relis, et moins je la trouve correcte.... disons qu'elle doit plus ou moins fonctionner.... J'y rejette un oeil...
	00

11/08/2011, 17h14	#10
ghosty177 Futur Membre du Club Inscription : juin 2007 Messages : 69 Détails du profil Informations forums : Inscription : juin 2007 Messages : 69 Points : 15 Points : 15	Ok ca a l'air good maintenant, donc merci beaucoup. je verrais pour l'optimisation plus tard (après les vac ). merci aieeeuuuuu.
	00

23/08/2011, 14h58	#13
ghosty177 Futur Membre du Club Inscription : juin 2007 Messages : 69 Détails du profil Informations forums : Inscription : juin 2007 Messages : 69 Points : 15 Points : 15	Salut aieeeuuuuu, Bon finalement je n'ai pas annulé mes vacances donc me revoilà. Je viens d'effectuer quelques test et ta dernière requête semble me retourner les résultats plus rapidement et avec un seul résultat à chaque fois. Du coup je pense rester sur celle la. Je te remercie pour ton aide. Bonne continuation. Ghosty
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email