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 189 190
| SELECT
a.REF as REFERENCE,
a.EVO as EVOLUTION,
a.REFGRP as REFERENCE_GROUPE,
a.TYP as TYPE,
acfgcyclevie.VALEUR as CYCLEDEVIE,
acfgcouleur.VALEUR as COULEUR,
acfgforme.VALEUR as FORME,
acfgcoupe.VALEUR as COUPE,
acad.TECHCBBASE as BASE,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp1.VALEUR, ';'),
CONCAT(acfgappforme1.VALEUR, ';')), CONCAT(acadapp1.TECHAPPQTE, ';')),
CONCAT(amattyp1.TXT, ';')), CONCAT(acfgapprevet1.VALEUR, ';')),
CONCAT(acfgappterm1.VALEUR, ';')), CONCAT(acadapp1.TECHAPPVOLUME, ';')),
CONCAT(acadapp1.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP2,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp2.VALEUR, ';'),
CONCAT(acfgappforme2.VALEUR, ';')), CONCAT(acadapp2.TECHAPPQTE, ';')),
CONCAT(amattyp2.TXT, ';')), CONCAT(acfgapprevet2.VALEUR, ';')),
CONCAT(acfgappterm2.VALEUR, ';')), CONCAT(acadapp2.TECHAPPVOLUME, ';')),
CONCAT(acadapp1.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP2,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp3.VALEUR, ';'),
CONCAT(acfgappforme3.VALEUR, ';')), CONCAT(acadapp3.TECHAPPQTE, ';')),
CONCAT(amattyp3.TXT, ';')), CONCAT(acfgapprevet3.VALEUR, ';')),
CONCAT(acfgappterm3.VALEUR, ';')), CONCAT(acadapp3.TECHAPPVOLUME, ';')),
CONCAT(acadapp3.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP3,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp4.VALEUR, ';'),
CONCAT(acfgappforme4.VALEUR, ';')), CONCAT(acadapp4.TECHAPPQTE, ';')),
CONCAT(amattyp4.TXT, ';')), CONCAT(acfgapprevet4.VALEUR, ';')),
CONCAT(acfgappterm4.VALEUR, ';')), CONCAT(acadapp4.TECHAPPVOLUME, ';')),
CONCAT(acadapp4.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP4,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp5.VALEUR, ';'),
CONCAT(acfgappforme5.VALEUR, ';')), CONCAT(acadapp5.TECHAPPQTE, ';')),
CONCAT(amattyp5.TXT, ';')),CONCAT(acfgapprevet5.VALEUR, ';')),
CONCAT(acfgappterm5.VALEUR, ';')), CONCAT(acadapp5.TECHAPPVOLUME, ';')),
CONCAT(acadapp5.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP5,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp6.VALEUR, ';'),
CONCAT(acfgappforme6.VALEUR, ';')), CONCAT(acadapp6.TECHAPPQTE, ';')),
CONCAT(amattyp6.TXT, ';')), CONCAT(acfgapprevet6.VALEUR, ';')),
CONCAT(acfgappterm6.VALEUR, ';')), CONCAT(acadapp6.TECHAPPVOLUME, ';')),
CONCAT(acadapp6.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP6,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp7.VALEUR, ';'),
CONCAT(acfgappforme7.VALEUR, ';')), CONCAT(acadapp7.TECHAPPQTE, ';')),
CONCAT(amattyp7.TXT, ';')), CONCAT(acfgapprevet7.VALEUR, ';')),
CONCAT(acfgappterm7.VALEUR, ';')), CONCAT(acadapp7.TECHAPPVOLUME, ';')),
CONCAT(acadapp7.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP7,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(acfgapp8.VALEUR, ';'),
CONCAT(acfgappforme8.VALEUR, ';')), CONCAT(acadapp8.TECHAPPQTE, ';')),
CONCAT(amattyp8.TXT, ';')), CONCAT(acfgapprevet8.VALEUR, ';')),
CONCAT(acfgappterm8.VALEUR, ';')), CONCAT(acadapp8.TECHAPPVOLUME, ';')),
CONCAT(acadapp8.TECHCBAPPLUMINOVA, ';')) AS APPLIQUE_GROUP8,
(SELECT (CONCAT(CONCAT(acfgdecalq.VALEUR,' '), acfgdecalqcouleur.VALEUR))
FROM ARTCADDECALQ acaddecalq
LEFT JOIN ARTCFGLST acfgdecalq
ON acfgdecalq.IDLST = acaddecalq.TECHDECALQ
LEFT JOIN ARTCFGLST acfgdecalqcouleur
ON acfgdecalqcouleur.IDLST = acaddecalq.MKGDECALQCOULEUR WHERE acaddecalq.NOART = a.NOART) as DECALQUE
FROM ART a
LEFT JOIN ARTBT abt
ON abt.NOART = a.NOART
LEFT JOIN ARTCAD acad
ON acad.NOART = a.NOART
LEFT JOIN ARTCFGLST acfgcyclevie
ON acfgcyclevie.IDLST = abt.CYCLEVIE
LEFT JOIN ARTCFGLST acfgcouleur
ON acfgcouleur.IDLST = acad.TECHCOULEUR
LEFT JOIN ARTCFGLST acfgforme
ON acfgforme.IDLST = abt.FORME
LEFT JOIN ARTCFGLST acfgcoupe
ON acfgcoupe.IDLST = acad.TECHCOUPE
LEFT JOIN ARTCADAPPLIQUE acadapp1
ON acadapp1.NOART = a.NOART AND acadapp1.GRP = 1
LEFT JOIN ARTCADAPPLIQUE acadapp2
ON acadapp2.NOART = a.NOART AND acadapp2.GRP = 2
LEFT JOIN ARTCADAPPLIQUE acadapp3
ON acadapp3.NOART = a.NOART AND acadapp3.GRP = 3
LEFT JOIN ARTCADAPPLIQUE acadapp4
ON acadapp4.NOART = a.NOART AND acadapp4.GRP = 4
LEFT JOIN ARTCADAPPLIQUE acadapp5
ON acadapp5.NOART = a.NOART AND acadapp5.GRP = 5
LEFT JOIN ARTCADAPPLIQUE acadapp6
ON acadapp6.NOART = a.NOART AND acadapp6.GRP = 6
LEFT JOIN ARTCADAPPLIQUE acadapp7
ON acadapp7.NOART = a.NOART AND acadapp7.GRP = 7
LEFT JOIN ARTCADAPPLIQUE acadapp8
ON acadapp8.NOART = a.NOART AND acadapp8.GRP = 8
LEFT JOIN ARTCFGLST acfgapp1
ON acfgapp1.IDLST = acadapp1.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp2
ON acfgapp2.IDLST = acadapp2.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp3
ON acfgapp3.IDLST = acadapp3.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp4
ON acfgapp4.IDLST = acadapp4.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp5
ON acfgapp5.IDLST = acadapp5.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp6
ON acfgapp6.IDLST = acadapp6.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp7
ON acfgapp7.IDLST = acadapp7.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgapp8
ON acfgapp8.IDLST = acadapp8.TECHAPPLIQUE
LEFT JOIN ARTCFGLST acfgappforme1
ON acfgappforme1.IDLST = acadapp1.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme2
ON acfgappforme2.IDLST = acadapp2.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme3
ON acfgappforme3.IDLST = acadapp3.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme4
ON acfgappforme4.IDLST = acadapp4.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme5
ON acfgappforme5.IDLST = acadapp5.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme6
ON acfgappforme6.IDLST = acadapp6.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme7
ON acfgappforme7.IDLST = acadapp7.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgappforme8
ON acfgappforme8.IDLST = acadapp8.TECHAPPFORME
LEFT JOIN ARTCFGLST acfgapprevet1
ON acfgapprevet1.IDLST = acadapp1.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet2
ON acfgapprevet2.IDLST = acadapp2.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet3
ON acfgapprevet3.IDLST = acadapp3.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet4
ON acfgapprevet4.IDLST = acadapp4.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet5
ON acfgapprevet5.IDLST = acadapp5.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet6
ON acfgapprevet6.IDLST = acadapp6.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet7
ON acfgapprevet7.IDLST = acadapp7.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgapprevet8
ON acfgapprevet8.IDLST = acadapp8.TECHAPPREVET
LEFT JOIN ARTCFGLST acfgappterm1
ON acfgappterm1.IDLST = acadapp1.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm2
ON acfgappterm2.IDLST = acadapp2.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm3
ON acfgappterm3.IDLST = acadapp3.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm4
ON acfgappterm4.IDLST = acadapp4.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm5
ON acfgappterm5.IDLST = acadapp5.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm6
ON acfgappterm6.IDLST = acadapp6.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm7
ON acfgappterm7.IDLST = acadapp7.TECHAPPTERM
LEFT JOIN ARTCFGLST acfgappterm8
ON acfgappterm8.IDLST = acadapp8.TECHAPPTERM
LEFT JOIN ARTMAT amat1
ON amat1.NOARTMAT = acadapp1.NOARTMAT
LEFT JOIN ARTMATTYP amattyp1
ON amat1.CODEMATIERE = amattyp1.TYP
LEFT JOIN ARTMAT amat2
ON amat2.NOARTMAT = acadapp2.NOARTMAT
LEFT JOIN ARTMATTYP amattyp2
ON amat2.CODEMATIERE = amattyp2.TYP
LEFT JOIN ARTMAT amat3
ON amat3.NOARTMAT = acadapp3.NOARTMAT
LEFT JOIN ARTMATTYP amattyp3
ON amat3.CODEMATIERE = amattyp3.TYP
LEFT JOIN ARTMAT amat4
ON amat4.NOARTMAT = acadapp4.NOARTMAT
LEFT JOIN ARTMATTYP amattyp4
ON amat4.CODEMATIERE = amattyp4.TYP
LEFT JOIN ARTMAT amat5
ON amat5.NOARTMAT = acadapp5.NOARTMAT
LEFT JOIN ARTMATTYP amattyp5
ON amat5.CODEMATIERE = amattyp5.TYP
LEFT JOIN ARTMAT amat6
ON amat6.NOARTMAT = acadapp6.NOARTMAT
LEFT JOIN ARTMATTYP amattyp6
ON amat6.CODEMATIERE = amattyp6.TYP
LEFT JOIN ARTMAT amat7
ON amat7.NOARTMAT = acadapp7.NOARTMAT
LEFT JOIN ARTMATTYP amattyp7
ON amat7.CODEMATIERE = amattyp7.TYP
LEFT JOIN ARTMAT amat8
ON amat8.NOARTMAT = acadapp8.NOARTMAT
LEFT JOIN ARTMATTYP amattyp8
ON amat8.CODEMATIERE = amattyp8.TYP
WHERE a.TYP = 'CAD' |
Partager