Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 13 sur 13
  1. #1
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut Recursivité CTE avec SQL Server

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

  2. #2
    Modérateur

    Avatar de elsuket
    Homme Profil pro Nicolas Souquet
    Administrateur de base de données
    Inscrit en
    janvier 2005
    Messages
    5 137
    Détails du profil
    Informations personnelles :
    Nom : Homme Nicolas Souquet
    Âge : 33
    Localisation : Thaïlande

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2005
    Messages : 5 137
    Points : 10 295
    Points
    10 295

    Par défaut

    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 ?

    @++
    "Les mots sont des mots, les explications des explications, les promesses des promesses; mais seule la performance est tangible." Harold Geneen.
    Blog | Profil | MVP SQL Server | Consulter ou télécharger les fichiers d'aide de SQL Server, des versions 2000 à 2012

  3. #3
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut

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

  4. #4
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

    Bonjour

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

    Code SQL :
    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)

  5. #5
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut

    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

  6. #6
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

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

  7. #7
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut

    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 :
    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 :
    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 :
    gwmet.numgwmet = Vue_Fil.metgwvue AND gwmet.langwmet = Vue_Fil.langwvue AND Vue_Fil.tvugwvue = 'F'

  8. #8
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

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

  9. #9
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

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

  10. #10
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut

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

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

    merci aieeeuuuuu.

  11. #11
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

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

  12. #12
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    3 131
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 3 131
    Points : 5 777
    Points
    5 777

    Par défaut

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

  13. #13
    Futur Membre du Club
    Inscrit en
    juin 2007
    Messages
    76
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 76
    Points : 18
    Points
    18

    Par défaut

    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

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •