dépassement limite de tables

Invité · 17/12/2008, 18h57

Bonjour,

Jai une procédure stocké de la forme :

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
SELECT COUNT(fields1), COUNT(fields2 ), COUNT(fields3) ..... COUNT(fieldsn)
FROM
(
SELECT...
jointures avec des vues & tables...
) AS deriv1 LEFT OUTER JOIN 
(
SELECT...
jointures avec des vues & tables...
) AS deriv2 ON deriv1.id = deriv2.id LEFT OUTER JOIN
(
SELECT...
jointures avec des vues & tables...
) AS deriv3 ON deriv2.id = deriv3.id  LEFT OUTER JOIN 
 
etc.....

Y'a t'il une solution pour remédier à ce probleme de limite de tables (256) sachant que jai minimiser au maximum le nombre de jointures dans les tables dérivées

**elsuket** · 17/12/2008, 23h08

Bonjour,

Sans le code complet, c'est difficile à dire.
Si vous utilisez effectivement 256 tables différentes dans votre requête, c'est qu'il y a un problème dans votre modèle de données ...

@++

**WOLO Laurent** · 18/12/2008, 09h33

C'est un grave probleme de modélisation.
Il faut déjà songer à changer cela sinon ton application ne fonctionnera pas.
Pense à un projet de SSIS pour fusionner les donner !
C'est le mieux quand puisse faire pour t'aider

Invité · 18/12/2008, 11h59

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spActualiserStatistiqueMedecinColon]
	-- Add the parameters for the stored procedure here
@intIDCampagne int, @dtmDebut datetime , @dtmFin datetime , @chrDep char(3)
 
AS
DECLARE @intNbCarDep int
SET @intNbCarDep = LEN(RTRIM(LTRIM(@chrDep)))
 
DECLARE @intGlobalCancer int
DECLARE @intGlobalRelance int
DECLARE @intGlobalAde int
DECLARE @intGlobalConsult int
DECLARE @intGlobalRemise int
DECLARE @intGlobalNonRemise int
DECLARE @intGlobalPlages int
DECLARE @intGlobalHemoccult int
DECLARE @intGlobalConservation int
DECLARE @intGlobalPrelevement int
DECLARE @intGlobalAutreCas int
DECLARE @intGlobalTestLus int
DECLARE @intGlobalTestNeg int
DECLARE @intGlobalTestPos int
DECLARE @intGlobalNonAna int
DECLARE @intGlobalColoscopie int
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
IF NOT EXISTS (SELECT SMC_INTID FROM T_STATMEDECINCOLON WHERE SMC_INTIDCAMPAGNE=@intIDCampagne)
INSERT INTO T_STATMEDECINCOLON(SMC_INTIDCAMPAGNE,	SMC_DTMDEBUT ,SMC_DTMFIN, SMC_MEDINTID,SMC_INTPERSOCONSULT	,	
SMC_INTPERSOAVECREMISE	,	SMC_INTPERSOSANSREMISE	,	SMC_INTPERSOTESTSLU	,	SMC_INTPERSORELANCE	,	
SMC_INTPERSOTESTNONANA	,SMC_INTPERSOMANQUE	,SMC_INTPERSOHEMMO	,SMC_INTPERSOCONSERV,SMC_INTPERSOPRELEV	,	
SMC_INTPERSOAUTRES,	SMC_INTPERSONEG	,SMC_INTPERSOPOS,SMC_INTPERSOCOLO,SMC_INTPERSOADE	,SMC_INTPERSOCANCER	,	
SMC_INTGLOBALCONSULT	,	
SMC_INTGLOBALAVECREMISE	,	
SMC_INTGLOBALSANSREMISE	,	
SMC_INTGLOBALTESTSLU	,	
SMC_INTGLOBALRELANCE	,	
SMC_INTGLOBALTESTNONANA	,	
SMC_INTGLOBALMANQUE	,	
SMC_INTGLOBALHEMMO	,	
SMC_INTGLOBALCONSERV	,	
SMC_INTGLOBALPRELEV	,	
SMC_INTGLOBALAUTRES	,	
SMC_INTGLOBALNEG	,	
SMC_INTGLOBALPOS	,	
SMC_INTGLOBALCOLO	,	
SMC_INTGLOBALADE	,	
SMC_INTGLOBALCANCER	)
SELECT @intIDCampagne , @dtmDebut , @dtmFin , derivedtbl_16.MED_INTID ,  COUNT(derivedtbl_14.INV_INTID) AS nbPersoConsult , COUNT(derivedtbl_10.INV_INTID) AS nbPersoAvecR , 
COUNT(derivedtbl_11.INV_INTID) AS nbPersoSansR , COUNT(derivedtbl_2.INV_INTID) AS nbPersoTests , 
COUNT(derivedtbl_15.INV_INTID) AS nbPersoRelance , COUNT(derivedtbl_9.INV_INTID) AS nbPersoTestNonAna ,
COUNT(derivedtbl_1.INV_INTID) AS nbPersoManque , COUNT(derivedtbl_16.INV_INTID) AS nbPersoHemmo ,
COUNT(derivedtbl_3.INV_INTID) AS nbPersoConserv , COUNT(derivedtbl_4.INV_INTID) AS nbPersoMauvais ,
COUNT(derivedtbl_5.INV_INTID) AS nbPersoAutres , COUNT(derivedtbl_6.INV_INTID) AS nbPersoTestNeg ,
COUNT(derivedtbl_7.INV_INTID) AS nbPersoTestPos , COUNT(derivedtbl_8.INV_INTID) AS nbPersoColo , 
COUNT(derivedtbl_13.INV_INTID) AS nbPersoAde , COUNT(derivedtbl_12.INV_INTID) AS nbPersoCancer ,
@intGlobalConsult AS nbGlobalConsult , @intGlobalRemise AS nbGlobalAvecR , @intGlobalNonRemise AS nbGlobalSansR, 
@intGlobalTestLus AS nbGlobalTests ,@intGlobalRelance AS nbGlobalRelance ,
@intGlobalNonAna AS nbGlobalTestNonAna , @intGlobalPlages AS nbGlobalManque, @intGlobalHemoccult  AS nbGlobalHemmo ,
@intGlobalConservation  AS nbGlobalConserv , @intGlobalPrelevement  AS nbGlobalMauvais,
@intGlobalAutreCas AS nbGlobalAutres  , @intGlobalTestNeg AS nbGlobalTestNeg, @intGlobalTestPos AS nbGlobalTestPos, 
@intGlobalColoscopie AS nbGlobalColo , @intGlobalAde AS nbGlobalAde , @intGlobalCancer AS nbGlobalCancer
 
FROM (        (
				SELECT  vListeTestRemis.MED_INTID , dbo.vInvs_LectureColon.INV_INTID
                FROM  dbo.vInvs_LectureColon
				INNER JOIN dbo.vListeTestRemis ON dbo.vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
				INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND Cit_vchLibelle='Manque de plages' AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				) AS derivedtbl_1 LEFT OUTER JOIN
 
				(
				SELECT  vListeTestRemis.MED_INTID , dbo.vInvs_LectureColon.INV_INTID
                FROM  dbo.vInvs_LectureColon
				INNER JOIN dbo.vListeTestRemis ON dbo.vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
				INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				)  AS derivedtbl_2 ON derivedtbl_1.INV_INTID=derivedtbl_2.INV_INTID LEFT OUTER JOIN 
 
				(	
				SELECT  vListeTestRemis.MED_INTID , dbo.vInvs_LectureColon.INV_INTID
                FROM  dbo.vInvs_LectureColon
				INNER JOIN dbo.vListeTestRemis ON dbo.vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
				INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND Cit_vchLibelle='Défaut de conservation' AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				) AS derivedtbl_3 ON derivedtbl_3.INV_INTID=derivedtbl_2.INV_INTID LEFT OUTER JOIN 
 
				(
				SELECT  vListeTestRemis.MED_INTID , dbo.vInvs_LectureColon.INV_INTID
                FROM  dbo.vInvs_LectureColon
				INNER JOIN dbo.vListeTestRemis ON dbo.vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
				INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND Cit_vchLibelle='Mauvais prélèvement' AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				) AS derivedtbl_4 ON derivedtbl_3.INV_INTID=derivedtbl_4.INV_INTID LEFT OUTER JOIN 
 
			(
				SELECT  vListeTestRemis.MED_INTID , dbo.vInvs_LectureColon.INV_INTID
                FROM  dbo.vInvs_LectureColon
				INNER JOIN dbo.vListeTestRemis ON dbo.vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
				INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND Cit_vchLibelle='Autre cas' AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				)  AS derivedtbl_5 ON derivedtbl_4.INV_INTID=derivedtbl_5.INV_INTID LEFT OUTER JOIN 
 
				(
				SELECT (T_INVITATION.RES_INTID) , T_INVITATION.INV_INTID , vListeTestRemis.MED_INTID
						FROM T_INVITATION
						INNER JOIN vInvs_LectureColon ON  T_INVITATION.INV_INTID=vInvs_LectureColon.INV_INTID
						INNER JOIN T_RESULTATDEP ON T_INVITATION.RES_INTID=T_RESULTATDEP.RES_INTID 
						INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE RES_CHRCODE='N' AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep)))) AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)				
					)  AS derivedtbl_6 ON derivedtbl_5.INV_INTID=derivedtbl_6.INV_INTID LEFT OUTER JOIN 
 
					(
					SELECT (T_INVITATION.RES_INTID) , T_INVITATION.INV_INTID , vListeTestRemis.MED_INTID
						FROM T_INVITATION
						INNER JOIN vInvs_LectureColon ON  T_INVITATION.INV_INTID=vInvs_LectureColon.INV_INTID
						INNER JOIN T_RESULTATDEP ON T_INVITATION.RES_INTID=T_RESULTATDEP.RES_INTID 
						INNER JOIN dbo.vListeTestRemis  ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE RES_CHRCODE='P' AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep)))) AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
					) AS derivedtbl_7 ON derivedtbl_6.INV_INTID=derivedtbl_7.INV_INTID LEFT OUTER JOIN 
 
					(
					SELECT vInvs_Coloscopie.REA_INTID , vInvs_Coloscopie.INV_INTID , vListeTestRemis.MED_INTID
						FROM vInvs_Coloscopie 
						INNER JOIN T_INVITATION ON vInvs_Coloscopie.INV_INTID=T_INVITATION.INV_INTID 
						INNER JOIN dbo.vListeTestRemis ON vInvs_Coloscopie.INV_INTID=dbo.vListeTestRemis.INV_INTID
						INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))  
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))  
						AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
						AND vInvs_Coloscopie.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
					) AS derivedtbl_8 ON derivedtbl_7.INV_INTID=derivedtbl_8.INV_INTID LEFT OUTER JOIN
 
					(
					SELECT vInvs_LectureColon.REA_INTID , vInvs_LectureColon.INV_INTID , vListeTestRemis.MED_INTID
						FROM vInvs_LectureColon 
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID=dbo.vListeTestRemis.INV_INTID
						INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID  
						WHERE REA_TINORDREININT <> 0 AND  vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep)))) AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
						AND vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
					)  AS derivedtbl_9 ON derivedtbl_8.INV_INTID=derivedtbl_7.INV_INTID LEFT OUTER JOIN
 
 
					(
					 SELECT vInvs_RemiseColon.REA_INTID , vInvs_RemiseColon.INV_INTID , vListeTestRemis.MED_INTID
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   	INNER JOIN dbo.vListeTestRemis ON vInvs_RemiseColon.INV_INTID=dbo.vListeTestRemis.INV_INTID
					WHERE vInvs_RemiseColon.tinRemise = 1 AND (vInvs_RemiseColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))  AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)		
					) AS derivedtbl_10 ON derivedtbl_9.INV_INTID=derivedtbl_10.INV_INTID LEFT OUTER JOIN
 
					(
					 SELECT vInvs_RemiseColon.REA_INTID , vInvs_RemiseColon.INV_INTID , vListeTestRemis.MED_INTID
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   	INNER JOIN dbo.vListeTestRemis ON vInvs_RemiseColon.INV_INTID=dbo.vListeTestRemis.INV_INTID
					WHERE vInvs_RemiseColon.tinRemise = 2 AND (vInvs_RemiseColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))  AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
					) AS derivedtbl_11 ON derivedtbl_10.INV_INTID=derivedtbl_11.INV_INTID LEFT OUTER JOIN
 
					(
					SELECT  vInvs_LectureColon.INV_INTID , vListeTestRemis.MED_INTID
					FROM  dbo.vInvs_LectureColon 
					INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
					INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
					INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
					 WHERE MOT_VCHCODE='cancer' AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin 
					AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL) AND (vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin)
				 ) AS derivedtbl_12 ON derivedtbl_11.INV_INTID=derivedtbl_12.INV_INTID LEFT OUTER JOIN
 
			(
 
			SELECT T_INVITATION.INV_INTID ,  vListeTestRemis.MED_INTID
			FROM T_INVITATION
			INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
			INNER JOIN dbo.vListeTestRemis ON T_INVITATION.INV_INTID = dbo.vListeTestRemis.INV_INTID
			INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
			WHERE MOT_VCHNOM='Adénome'
			AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
			AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
			AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL) AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
			) AS derivedtbl_13 ON derivedtbl_12.INV_INTID=derivedtbl_13.INV_INTID LEFT OUTER JOIN
 
				-- nb consultations
				(
				SELECT  vListeTestRemis.MED_INTID , T_REALEXAM.INV_INTID
                FROM  T_REALEXAM
				INNER JOIN dbo.vListeTestRemis ON T_REALEXAM.INV_INTID = dbo.vListeTestRemis.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_REALEXAM.PER_INTID=vPersonneAdresseActive.PER_INTID
				INNER JOIN T_INVITATION ON T_REALEXAM.INV_INTID=dbo.T_INVITATION.INV_INTID
                WHERE (dbo.vListeTestRemis.MED_INTID IS NOT NULL)
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
                AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				) AS derivedtbl_14 ON derivedtbl_13.INV_INTID=derivedtbl_14.INV_INTID LEFT OUTER JOIN
 
				-- nb de relances
 
				(
				SELECT T_INVITATION.INV_INTID ,  vListeTestRemis.MED_INTID
				FROM T_INVITATION
				INNER JOIN dbo.vListeTestRemis ON T_INVITATION.INV_INTID=dbo.vListeTestRemis.INV_INTID
				INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
				WHERE  (dbo.vListeTestRemis.MED_INTID IS NOT NULL) AND (INV_DTMRELANCE IS NOT NULL OR INV_DTMRELANCEREMIS IS NOT NULL) 
				AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				AND INV_DTMRELANCE BETWEEN @dtmDebut AND @dtmFin
				)  AS derivedtbl_15 ON derivedtbl_14.INV_INTID=derivedtbl_15.INV_INTID LEFT OUTER JOIN
 
				(
				SELECT  vInvs_LectureColon.INV_INTID , vListeTestRemis.MED_INTID
					FROM  dbo.vInvs_LectureColon 
					INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
					INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
					INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
					 WHERE MOT_VCHCODE='Hémoccult périmé' AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin 
					AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL) AND (vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin)
				) AS derivedtbl_16 ON derivedtbl_15.INV_INTID=derivedtbl_16.INV_INTID 
 
) 
 
 
GROUP BY derivedtbl_16.MED_INTID
-- GLOBAL ---
 
 
 
SET @intGlobalCancer = (
						SELECT  COUNT(vInvs_LectureColon.INV_INTID)
					FROM  dbo.vInvs_LectureColon 
					INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
					INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
					INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
					 WHERE MOT_VCHCODE='cancer' AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin 
					AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				AND (vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin)
				)
 
SET @intGlobalRelance =(
						SELECT COUNT(T_INVITATION.INV_INTID)
						FROM T_INVITATION
						INNER JOIN dbo.vListeTestRemis ON T_INVITATION.INV_INTID=dbo.vListeTestRemis.INV_INTID
						INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
						WHERE (INV_DTMRELANCE IS NOT NULL OR INV_DTMRELANCEREMIS IS NOT NULL) 
						AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						AND INV_DTMRELANCE BETWEEN @dtmDebut AND @dtmFin
						)
 
SET @intGlobalAde = (
					SELECT COUNT(T_INVITATION.INV_INTID)
					FROM T_INVITATION
					INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
					INNER JOIN dbo.vListeTestRemis ON T_INVITATION.INV_INTID = dbo.vListeTestRemis.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
					WHERE MOT_VCHNOM='Adénome'
					AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
					AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin
				)
 
 
SET @intGlobalConsult = ( SELECT COUNT(T_INVITATION.INV_INTID)
							FROM T_INVITATION
						INNER JOIN dbo.vListeTestRemis ON T_INVITATION.INV_INTID=dbo.vListeTestRemis.INV_INTID
						INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
						WHERE  (INV_DTMRELANCE IS NOT NULL OR INV_DTMRELANCEREMIS IS NOT NULL) 
						AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						AND INV_DTMRELANCE BETWEEN @dtmDebut AND @dtmFin
						)
 
SET @intGlobalRemise = ( SELECT COUNT(REA_INTID)
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   WHERE tinRemise = 1 AND (REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				)
 
SET @intGlobalNonRemise = ( SELECT COUNT(REA_INTID)
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   WHERE tinRemise = 2 AND (REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				)
 
SET @intGlobalPlages = (
					SELECT COUNT(REA_INTID) 
					FROM vInvs_LectureColon 
					INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
					INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
					WHERE Cit_vchLibelle='Manque de plages'
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
					AND REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				)
 
SET @intGlobalHemoccult = (
					SELECT COUNT(REA_INTID) 
					FROM vInvs_LectureColon 
					INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
					INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
					WHERE Cit_vchLibelle='Hémoccult périmé'
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
					AND REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
					)
 
SET @intGlobalConservation = (
						SELECT COUNT(REA_INTID) 
						FROM vInvs_LectureColon 
						INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
						WHERE Cit_vchLibelle='Défaut de conservation'
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
					    AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
					)
 
SET @intGlobalPrelevement = (
						SELECT COUNT(REA_INTID) 
						FROM vInvs_LectureColon 
						INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
						WHERE Cit_vchLibelle='Mauvais prélèvement' AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						 AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
						)
 
SET @intGlobalAutreCas = (
					SELECT COUNT(REA_INTID) 
					FROM vInvs_LectureColon 
					INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
					INNER JOIN T_CAUSEININT ON vInvs_LectureColon.INTCAUSEININT=T_CAUSEININT.Cit_intID  
					WHERE Cit_vchLibelle='Autre cas' AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
					 AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
					)
 
SET @intGlobalTestLus = (
							SELECT COUNT(REA_INTID) 
							FROM vInvs_LectureColon 
							INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
							INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID 
							WHERE REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin 
							AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
							AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						)
 
SET @intGlobalTestNeg = (
						SELECT COUNT(T_INVITATION.RES_INTID) 
						FROM T_INVITATION
						INNER JOIN T_RESULTATDEP ON T_INVITATION.RES_INTID=T_RESULTATDEP.RES_INTID 
						INNER  JOIN vInvs_LectureColon ON vInvs_LectureColon.INV_INTID = T_INVITATION.INV_INTID 
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE RES_CHRCODE='N' AND REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						)
 
SET @intGlobalTestPos = (
						SELECT COUNT(T_INVITATION.RES_INTID) 
						FROM T_INVITATION 
						INNER JOIN T_RESULTATDEP ON T_INVITATION.RES_INTID=T_RESULTATDEP.RES_INTID 
						INNER  JOIN vInvs_LectureColon ON vInvs_LectureColon.INV_INTID = T_INVITATION.INV_INTID  
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE RES_CHRCODE='P' AND REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						)
 
SET @intGlobalNonAna = (
						SELECT COUNT(REA_INTID) 
						FROM vInvs_LectureColon 
						INNER JOIN vPersonneAdresseActive ON vInvs_LectureColon.PER_INTID = vPersonneAdresseActive.PER_INTID
						INNER JOIN T_INVITATION ON vInvs_LectureColon.INV_INTID=T_INVITATION.INV_INTID  
						WHERE REA_TINORDREININT <> 0 AND  REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
						)
 
SET @intGlobalColoscopie = (
						SELECT COUNT(REA_INTID) 
						FROM vInvs_Coloscopie 
						INNER JOIN T_INVITATION ON vInvs_Coloscopie.INV_INTID=T_INVITATION.INV_INTID 
						INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
						WHERE REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))  
						AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))  
					)
 
 
 
 
END

c'est un peu long... mais bon

**SQLpro** · 18/12/2008, 13h18

Vous avez beaucoup de requêtes inutiles !

Un petit exemple :

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
SET @intGlobalRemise = ( SELECT COUNT(REA_INTID)
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   WHERE tinRemise = 1 AND (REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				)
 
SET @intGlobalNonRemise = ( SELECT COUNT(REA_INTID)
				   FROM vInvs_RemiseColon
				   INNER JOIN T_INVITATION ON vInvs_RemiseColon.INV_INTID=T_INVITATION.INV_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID = vPersonneAdresseActive.PER_INTID
				   WHERE tinRemise = 2 AND (REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin) AND (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne)) 
					AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				)

Une seule requête suffit en utilisant CASE et SUM.

A +

Invité · 18/12/2008, 14h16

les SET n'affectent pas l'erreur de limites de tables , l'erreur vient des tables dérivés avec les jointures externes car jai mis en commentaire tous les SET mais sa ne change rien

**WOLO Laurent** · 18/12/2008, 16h18

Il te faut réadapter ton model physique.
J'ai remarqué que ta table dérivée est multipliée selon le type d'information de l'invité qui y est stockée.

1-Créer une table t_derived avec un nouveau champs sta_id
2-Créer une table t_statut qui contiendra tous les status d'un invité (un malade apparemment)
Exemple :

manqué
Testé
Conservé
....
....

Inserer les données de toutes les tables derived_n dans la table créer tout en renseignant la colonne sta_id ajoutée recemment.

Et la requête est dès lors plus facile à construire.

Invité · 18/12/2008, 16h44

je suis en train dessayer avec des variables table
j'insert dans chaque variable une requete :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO @tHemoccult(MED_INTID , INV_INTID)	
				SELECT  vInvs_LectureColon.INV_INTID , vListeTestRemis.MED_INTID
					FROM  dbo.vInvs_LectureColon 
					INNER JOIN dbo.vListeTestRemis ON vInvs_LectureColon.INV_INTID = dbo.vListeTestRemis.INV_INTID
					INNER JOIN T_INVITATION ON dbo.vListeTestRemis.INV_INTID=dbo.T_INVITATION.INV_INTID
					INNER JOIN T_MOTIF ON T_INVITATION.MOT_INTID=T_MOTIF.MOT_INTID
					INNER JOIN vPersonneAdresseActive ON T_INVITATION.PER_INTID=vPersonneAdresseActive.PER_INTID
					 WHERE MOT_VCHCODE='Hémoccult périmé' AND dbo.vListeTestRemis.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin 
					AND  (@intIDCampagne = 0 OR (@intIDCampagne<>0 AND CAM_INTID = @intIDCampagne))
				AND (@intNbCarDep=0 OR (@intNbCarDep <> 0 AND (Substring(VIL_VCHCP,1,@intNbCarDep) = RTRIM(@chrDep))))
				AND (dbo.vListeTestRemis.MED_INTID IS NOT NULL) AND (vInvs_LectureColon.REA_DTMDATE BETWEEN @dtmDebut AND @dtmFin)

tu penses que sa peut le faire?

**WOLO Laurent** · 18/12/2008, 17h10

Le seul problème avec les variables tables, c'est que c'est volatil et donc non réutilisable !

Invité · 18/12/2008, 17h13

jpeu les reutiliser dans la procédure quand meme ? et est ce que je peux faire des jointures entre variable de table?

**WOLO Laurent** · 18/12/2008, 17h30

Oui possible de faire des jointures.
Voici même un exemple de la MSDN

Code :

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

1
2
3
4
5
 
SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID)

Invité · 18/12/2008, 17h34

oué mais on peut pas faire des jointures entre variable table apparemment

Jai rien dit

**elsuket** · 19/12/2008, 11h58

Bonjour,

Si on peut :

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
SET NOCOUNT ON
 
DECLARE @A TABLE
(
	a TINYINT
)
 
DECLARE @B TABLE
(
	b TINYINT
)
 
INSERT INTO @A VALUES (1)
INSERT INTO @A VALUES (2)
INSERT INTO @A VALUES (3)
INSERT INTO @A VALUES (4)
INSERT INTO @A VALUES (5)
 
INSERT INTO @B VALUES (3)
INSERT INTO @B VALUES (4)
INSERT INTO @B VALUES (5)
 
SELECT A.a
FROM @A A
JOIN @B B ON B.b = A.a

Retourne :

a
----
3
4
5

En revanche c'est assez contre-performant parce que les tables temporaires quelles qu'elles soient sont stockées dans TempDB ...

Avez-vous pensé à écrire une vue ?

@++

dépassement limite de tables

Développement SQL Server

Discussions similaires

Partager

Partager