Discussion :

[samsso2006]

Bonsoir a tous

Je souhaite creer une table a partir de 04 requetes:

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

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

[SQLpro]

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

A +

[SQL_EVAN]

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 :

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

[Rei Ichido]

Ou plus simplement :

Code :

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

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