Bonjour,

J'ai créé une requête SQL qui fonctionne parfaitement et je souhaite maintenant la mettre sous exel afin de pouvoir la lancer via un bouton.

J'ai sur le même classeur d'autres requêtes SQL attachées de cette manière et je ne rencontre pas de problèmes particulier.

Et la, j'ai un message d'erreur me disant que la syntaxe vers ':' est incorrect.

Pouvez vous me dire s'il y a des particularités avec les ':' ou si mon code comporte des ereurs ?
Merci d'avance,


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
sSQL = sSQL + " SELECT   DD.PARC_CONTRACTUEL,CC.CODE_TYPECNT,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 1 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_01,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 2 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_02,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 3 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_03,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 4 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_04,"
sSQL = sSQL + "         SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 5 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_05,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 6 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_06,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 7 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_07,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 8 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_08,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 9 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_09,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 10 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_10,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 11 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_11,"
sSQL = sSQL + "          SUM(CASE"
sSQL = sSQL + "                 WHEN (MONTH(DD.DTE_FACT)-1) = 12 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + "                 ELSE 0"
sSQL = sSQL + "              END) AS KMS_PARCOURUS_12,"
sSQL = sSQL + "          SUM(CAST(DD.KMS_FACT as NUMERIC)) AS KMS_FACT,"
sSQL = sSQL + "          MAX(CAST(DD.PX_UNITAIRE as money)) AS PX_UNITAIRE,"
sSQL = sSQL + "          SUM(CAST(DD.FACT_MONTANTNET as money)) AS FACT_MONTANTNET,"
sSQL = sSQL + "          SUM(CAST(PP.KMINCLUS as NUMERIC)) AS KMINCLUS"
sSQL = sSQL + " FROM     (SELECT AA.ID_FACT,"
sSQL = sSQL + "                  AA.DTE_FACT,"
sSQL = sSQL + "                  AA.DTEDEB,"
sSQL = sSQL + "                  AA.DTEFIN,"
sSQL = sSQL + "                  AA.ID_CLIENTFICHE,"
sSQL = sSQL + "                  AA.FACT_NUMPARC,"
sSQL = sSQL + "                  (CASE"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + "                           AND AA.FACT_NUMPARC IS NULL) THEN 'PAS DE CONTRAT'"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + "                           AND AA.FACT_NUMPARC IS NOT NULL) THEN AA.FACT_NUMPARC"
sSQL = sSQL + "                     WHEN (BB.PARC_ID IS NOT NULL) THEN BB.PARC_ID"
sSQL = sSQL + "                     Else: BB.PARC_ID"
sSQL = sSQL + "                   END) AS 'PARC_CONTRACTUEL',"
sSQL = sSQL + "                  BB.VERSION_CONTRAT,"
sSQL = sSQL + "                  AA.AAAAMM,"
sSQL = sSQL + "                  AA.TYPE_LIBELLEPROP,"
sSQL = sSQL + "                  AA.KMS_PARCOURUS,"
sSQL = sSQL + "                  AA.KMS_FACT,"
sSQL = sSQL + "                  AA.PX_UNITAIRE,"
sSQL = sSQL + "                  AA.FACT_MONTANTNET,"
sSQL = sSQL + "                  AA.TYPE_PRST,"
sSQL = sSQL + "                  AA.CODE_AGENCE,"
sSQL = sSQL + "                  AA.NO_CONTRAT,"
sSQL = sSQL + "                  AA.NUM_FACT"
sSQL = sSQL + "           FROM   (SELECT B.ID_FACT,"
sSQL = sSQL + "                          B.NUM_LIGNE,"
sSQL = sSQL + "                          H.DTE_FACT,"
sSQL = sSQL + "                          B.DTEDEB,"
sSQL = sSQL + "                          B.DTEFIN,"
sSQL = sSQL + "                          H.ID_CLIENTFICHE,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN B.FACT_NUMPARC IS NULL THEN (SELECT   TOP 1 LL.FACT_PARCID"
sSQL = sSQL + "                                                               FROM     S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + "                                                               WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                                                                        AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + "                                                                        AND TYPE_LIGNE = 'HE'"
sSQL = sSQL + "                                                               ORDER BY LL.NUM_LIGNE DESC)"
sSQL = sSQL + "                             Else: B.FACT_NUMPARC"
sSQL = sSQL + "                           END) AS 'FACT_NUMPARC',"
sSQL = sSQL + "                          H.AAAAMM,"
sSQL = sSQL + "                          B.TYPE_LIBELLEPROP,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart' THEN FACT_QTE"
sSQL = sSQL + "                             Else '0'"
sSQL = sSQL + "                           END) AS 'KMS_Parcourus',"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN TYPE_LIBELLEPROP = 'ref.typeprst.KM.aff' THEN FACT_QTE"
sSQL = sSQL + "                             Else '0'"
sSQL = sSQL + "                           END) AS 'KMS_FACT',"
sSQL = sSQL + "                          (COALESCE(B.PX_UNITAIRE,0))     AS 'PX_UNITAIRE',"
sSQL = sSQL + "                          (COALESCE(B.FACT_MONTANTNET,0)) AS 'FACT_MONTANTNET',"
sSQL = sSQL + "                          B.TYPE_PRST,"
sSQL = sSQL + "                          B.CODE_AGENCE,"
sSQL = sSQL + "                          (CASE"
sSQL = sSQL + "                             WHEN B.NO_CONTRAT IS NULL THEN (SELECT TOP 1 LL.NO_CONTRAT"
sSQL = sSQL + "                                                             FROM   S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + "                                                             WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                                                                    AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + "                                                                    AND LL.CODE_TYPEPRST = 'LO')"
sSQL = sSQL + "                             Else: B.NO_CONTRAT"
sSQL = sSQL + "                           END) AS 'NO_CONTRAT',"
sSQL = sSQL + "                          H.NUM_FACT"
sSQL = sSQL + "                   FROM   S1SRVBDD02.IGLOO.DBO.FACT_LIGNE B"
sSQL = sSQL + "                          LEFT JOIN S1SRVBDD02.IGLOO.DBO.FACT_HEADER H"
sSQL = sSQL + "                            ON H.ID_FACT = B.ID_FACT"
sSQL = sSQL + "                   WHERE  B.ID_FACT IN (SELECT ID_FACT"
sSQL = sSQL + "                                        FROM S1SRVBDD02.IGLOO.DBO.FACT_HEADER"
sSQL = sSQL + "                                        WHERE  CODE_TYPECNT = 'LD'"
sSQL = sSQL + "                                               AND DTE_FACT BETWEEN '20100101' AND '20101231'"
sSQL = sSQL + "                                               AND CODE_SOCPF = 'PFLO'"
sSQL = sSQL + "                                               AND ID_CLIENTFICHE <> '5007798'"
sSQL = sSQL + "                                               AND ID_CLIENTFICHE IN (SELECT SUBSTRING(NUM_CLIENT,3,9)"
sSQL = sSQL + "                                                                      FROM S1SRVBDD02.Z_STATS.DBO.LISTEGRDSCPTES"
sSQL = sSQL + "                                                                      WHERE  GROUPE_PRINCIPAL LIKE '%BRAKE%'))"
sSQL = sSQL + "                          AND (TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart'"
sSQL = sSQL + "                                OR CODE_TYPEPRST = 'KM')"
sSQL = sSQL + "                          AND H.NUM_FACT IS NOT NULL"
sSQL = sSQL + "                          AND H.IS_COMPTA_CEGID = '1') AA"
sSQL = sSQL + "                  LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR BB"
sSQL = sSQL + "                    ON AA.NO_CONTRAT = BB.NO_CONTRAT"
sSQL = sSQL + "                       AND (CASE"
sSQL = sSQL + "                              WHEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + "                                    FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                    WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + "                                           AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099'))) IS NOT NULL THEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + "                                                                                                                                             FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                                                                                                                             WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + "                                                                                                                                                    AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099')))"
sSQL = sSQL + "                              ELSE (SELECT MAX(VERSION_CONTRAT)"
sSQL = sSQL + "                                    FROM   S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + "                                    WHERE  AA.NO_CONTRAT = CCC.NO_CONTRAT)"
sSQL = sSQL + "                            END) = BB.VERSION_CONTRAT) DD"
sSQL = sSQL + "                 "
sSQL = sSQL + "          LEFT JOIN S1SRVBDD02.IGLOO.DBO.CNT_PRST PP"
sSQL = sSQL + "            ON PP.NO_CONTRAT = DD.NO_CONTRAT"
sSQL = sSQL + "               AND PP.VERSION_CONTRAT = DD.VERSION_CONTRAT"
sSQL = sSQL + "               AND PP.CODE_TYPEPRST = 'KM'"
sSQL = sSQL + "          LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT CC"
sSQL = sSQL + "            ON PP.NO_CONTRAT = CC.NO_CONTRAT"
sSQL = sSQL + "            "
sSQL = sSQL + " GROUP BY DD.PARC_CONTRACTUEL, CC.CODE_TYPECNT"
sSQL = sSQL + " ORDER BY DD.PARC_CONTRACTUEL"