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