Incorrect syntax near the keyword 'as'

Version imprimable

Bonsoir a tous

Je souhaite creer une table a partir de 04 requetes:
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 create table #stat( [AccesTerreOui] [int] NULL, [AccesTerre_Non] [int] NULL, [AccesTerre_PasAvant] [int] NULL, [AccesTerre_PasdeReponse] [int] NULL) -- [TypePbmCode] [int] NULL --insertion de données statistiques dans la table stat--- insert INTO #stat select A.AccesTerreOui, A.AccesTerre_Non, A.AccesTerre_PasAvant, A.AccesTerre_PasdeReponse from ( (SELECT COUNT(distinct [79627X1X60]) from dbo.lime_survey_79627 where [79627X2X13] = 1) as AccesTerreOui, (select count(distinct [79627X1X60]) from dbo.lime_survey_79627 where [79627X2X13] =2) as AccesTerre_Non, (select count(distinct [79627X1X60]) from dbo.lime_survey_79627 where [79627X2X13] =3) as AccesTerre_PasAvant, (select count(distinct [79627X1X60]) from dbo.lime_survey_79627 where [79627X2X13] not in ('1','2','3')) as AccesTerre_PasdeReponse ) [A] select * from #stat --drop table #stat
Mais ca ne fonctionne pas.:(
A l’exécution je recois les messages d'erreurs suivants:

Citation:

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'as'.

Merci d'avance:D

06/09/2013, 09h13
SQLpro

Vous faites quoi ???? Un produit cartésien ???

A +

Hmmmm, il manque des jointure entre vos sous-requêtes/tables temporaires là. Ce qui est impossible puisque c'est des COUNT

Vous voulez pas plutôt faire cela:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
INSERT INTO #stat
	SELECT	(SELECT
				COUNT(DISTINCT [79627X1X60])
			FROM dbo.lime_survey_79627
			WHERE [79627X2X13] = 1)
			AS AccesTerreOui,
			(SELECT
				COUNT(DISTINCT [79627X1X60])
			FROM dbo.lime_survey_79627
			WHERE [79627X2X13] = 2)
			AS AccesTerre_Non,
			(SELECT
				COUNT(DISTINCT [79627X1X60])
			FROM dbo.lime_survey_79627
			WHERE [79627X2X13] = 3)
			AS AccesTerre_PasAvant,
			(SELECT
				COUNT(DISTINCT [79627X1X60])
			FROM dbo.lime_survey_79627
			WHERE [79627X2X13] NOT IN ('1', '2', '3'))
			AS AccesTerre_PasdeReponse

Ou plus simplement :

Code:

1
2
3
4
5
6
7
 
INSERT INTO #stat
SELECT COUNT(DISTINCT CASE WHEN [79627X2X13] = 1 THEN [79627X1X60] ELSE NULL END) AS AccesTerreOui
      ,COUNT(DISTINCT CASE WHEN [79627X2X13] = 2 THEN [79627X1X60] ELSE NULL END) AS AccesTerre_Non
      ,COUNT(DISTINCT CASE WHEN [79627X2X13] = 3 THEN [79627X1X60] ELSE NULL END) AS AccesTerre_PasAvant
      ,COUNT(DISTINCT CASE WHEN [79627X2X13] NOT IN (1,2,3) THEN [79627X1X60] ELSE NULL END) AS AccesTerre_PasdeReponse
 FROM lime_survey_79627