Convertir une requête SQL Server en requête Access

**facteur** · 25/04/2014, 15h10

Bonjour,
Un collègue m'a refilé une requête sql server.
J'essaie de la retranscrire en sql access mais en vain.
J'ai un message d'erreur

" erreur de synthaxe dans la clause FROM"

Pourriez-vous m'aidez..merci

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
SELECT DisplayName, Sum (c1) AS keyboard, Sum(c2) AS OHScanner, SUM(c3) AS HHScanner, SUM(c4) AS Stray,SUM(c5) AS Fragile, SUM(c6) AS Cylindrical,SUM(c7) AS OverSize , SUM (c1) + SUM(c2) + SUM(c4) + SUM(c5) + SUM(c6) + SUM(c7) AS Total
 
 
FROM (SELECT p.DisplayName,COUNT(*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
                  FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwItemData as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Keyboard') 
 
Group BY p.Displayname
 
Union
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwItemData as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='scanner') 
Group BY p.Displayname
 
UNION
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwItemData as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='HHScanner') 
Group BY p.Displayname
 
UNION
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwItemData as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Stray') 
Group BY p.Displayname
 
UNION
 
 
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwItemData as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='fragile') 
Group BY p.Displayname
 
UNION
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5, SUM(CounterValue) AS c6, 0 As c7,0 AS c8,0 AS c9
FROM dbo_vwPartCountsHistory AS ch WITH  INNER JOIN dbo_vwParts AS p WITH ON ch.partid=p.id
where( dbo_vwPartCountsHistory.CounterTypeId=82/*Cylindrical*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 Group BY p.Displayname
 
UNION
 
SELECT p. DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,  AS c6,SUM(CounterValue) As c7,0 AS c8,0 AS c9
FROM dbo_vwPartCountsHistory AS ch WITH  INNER JOIN dbo_vwParts AS p WITH ON ch.partid=p.id
where( dbo_vwPartCountsHistory.CounterTypeId=83/*OverSizel*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 Group BY p.Displayname

**ced** · 25/04/2014, 15h24

Bonjour,

Après remise en forme de la requête, on s'aperçoit qu'elle n'est pas complète

.
Il manque au moins une parenthèse à la fin et un alias...

De plus, je ne sais pas à quoi correspondent les WITH dans les jointures...

**facteur** · 05/05/2014, 15h16

bonjour ced,
merci pour cette reponse..
je vais revoir cette requete de A à Z..

**facteur** · 07/05/2014, 15h06

Bonjour CED,

Effectivement il manquait une parenthèse à la fin du code SQL.
Lorsque tu écris

il manque un alias à la fin

qu'est-ce qu'un alias ?..

**ced** · 07/05/2014, 15h20

Vous les utilisez déjà : ... AS p par exemple.
Mais comme vous faites une sous-requête dès le premier FROM, il vous faut un alias, ... AS t par exemple, derrière votre parenthèse finale (celle qui manque).

**facteur** · 07/05/2014, 15h27

CED,
Lorsque j'affiche la requête sql en mode création, je vois afficher ma requête avec les champs et au-dessus des champs, j'ai une table vide nommée bizarrement

%$##@_Alias

Qu'est ce que cela signifie ???

**f-leb** · 07/05/2014, 15h36

Cette table vide correspond justement à une sous-requête, lorsqu'il n'y a pas d'alias précisé l'assistant en rajoute un tout seul as Nom_d_Alias_a_la_#@¤$*.

**ced** · 07/05/2014, 15h36

Désolé, là, je passe la main. Je n'utilise pas et ne connais donc pas Access...
Du coup, je ne sais pas à quoi ça correspond.
C'est peut-être une question à poser sur le forum dédié aux requêtes Access.

**facteur** · 07/05/2014, 15h55

Merci CED..
F_leb, est-ce normale que ma table qui correspond à ma sous-requête soit vide ?
J' avoue que je ne fais jamais de sous-requêtes...

**facteur** · 14/05/2014, 10h47

Bonjour F_leb

J'ai réecris ma requete sql mais lorsque je l'éxecute, j 'ai un message d'erreur qui s'affiche :

"erreur de synthaxe dans la declaration de with owner access option"

Et lorque j'affiche ma requete en mode création ma table "tbl" est vide.
je suis perdu...

Voici ma requete sql :

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
SELECT DisplayName AS antennes, Sum(c1) AS keyboard, Sum(c2) AS OHScanner, Sum(c3) AS HHScanner, Sum(c4) AS Stray, Sum(c5) AS Fragile, Sum(c6) AS Cylindrical, Sum(c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
FROM [SELECT dbo_vwParts.Displayname,COUNT(*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
                  FROM dbo_vwItemEventHistory with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwParts as p with on id.inductionPartId=dbo_vwParts.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Keyboard') 
 
Group BY p.DisplayN ame
 
Union
 
SELECT p.DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwParts as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='scanner') 
 
Group BY p.DisplayName
 
UNION
 
SELECT p.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwParts as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='HHScanner') 
 
Group BY p.DisplayName
 
UNION
 
SELECTdbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory  with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwParts as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Stray') 
 
Group BY p.DisplayName
 
UNION
 
SELECT dbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwItemEventHistory with inner join dbo_vwItemData as id with on dbo_vwItemEventHistory.itemid=id.itemid inner join dbo_vwParts as p with on id.inductionPartId=p.id
where( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='fragile') 
 
Group BY p.DisplayName
 
UNION
 
SELECT dbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5, SUM(CounterValue) AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwPartCountsHistory AS ch WITH  INNER JOIN dbo_vwParts AS p WITH ON ch.partid=p.id
where( dbo_vwPartCountsHistory.CounterTypeId=82/*Cylindrical*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 Group BY dbo_vwParts
 
UNION
 
SELECT p.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,  AS c6,SUM(CounterValue) As c7,0 AS c8,0 AS c9
 
FROM dbo_vwPartCountsHistory AS ch WITH  INNER JOIN dbo_vwParts AS p WITH ON ch.partid=p.id
where( dbo_vwPartCountsHistory.CounterTypeId=83/*OverSizel*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 
 Group BY dbo_vwParts.Displayname]. AS tbl
 
ORDER BY DisplayName;

**Waldar** · 14/05/2014, 15h45

Voici le manuel de résolution de votre problème en trois étapes.

1. LIRE la première réponse qui vous a été donnée :

De plus, je ne sais pas à quoi correspondent les WITH dans les jointures...

2. LIRE votre message d'erreur :

"erreur de synthaxe dans la declaration de WITH owner access option"

3. Trouvez la corrélation entre ceux deux résultats (un léger indice devrait vous aider).

**facteur** · 14/05/2014, 16h05

J'ai supprimé le terme with dans la requete sql..
et lorsque je l'éxecute j'ai un autre message d'erreur cette fois :

erreur de synthaxe dans l'operation JOIN

J 'en aie marre....

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
SELECT DisplayName AS antennes, Sum(c1) AS keyboard, Sum(c2) AS OHScanner, Sum(c3) AS HHScanner, Sum(c4) AS Stray, Sum(c5) AS Fragile, Sum(c6) AS Cylindrical, Sum(c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
FROM (SELECT dbo_vwParts.Displayname,COUNT(*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
                  FROM (dbo_vwItemEventHistory INNER JOIN  dbo_vwItemData as id on dbo_vwItemEventHistory.ItemID=id.ItemID) inner join dbo_vwParts as p ON id.inductionPartID=dbo_vwParts.ID
 
 
where ( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Keyboard') 
 
Group BY p.DisplayName
 
Union
 
SELECT p.DisplayName,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM (dbo_vwItemEventHistory inner join dbo_vwItemData as id  on dbo_vwItemEventHistory.itemid=id.itemid) inner join dbo_vwParts as p  on id.inductionPartId=p.id
where ( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='scanner') 
 
Group BY p.DisplayName
 
UNION
 
SELECT p.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM (dbo_vwItemEventHistory  inner join dbo_vwItemData as id  on dbo_vwItemEventHistory.itemid=id.itemid) inner join dbo_vwParts as p  on id.inductionPartId=p.id
where ( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='HHScanner') 
 
Group BY p.DisplayName
 
UNION
 
SELECTdbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM (dbo_vwItemEventHistory inner join dbo_vwItemData as id on dbo_vwItemEventHistory.itemid=id.itemid) inner join dbo_vwParts as p  on id.inductionPartId=p.id
where ( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Stray') 
 
Group BY p.DisplayName
 
UNION
 
SELECT dbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,0 AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM (dbo_vwItemEventHistory  inner join dbo_vwItemData as id  on dbo_vwItemEventHistory.itemid=id.itemid) inner join dbo_vwParts as p on id.inductionPartId=p.id
where ( dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='fragile') 
 
Group BY p.DisplayName
 
UNION
 
SELECT dbo_vwParts.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5, SUM(CounterValue) AS c6, 0 As c7,0 AS c8,0 AS c9
 
FROM dbo_vwPartCountsHistory AS ch  INNER JOIN dbo_vwParts AS p  ON ch.partid=p.id
where ( dbo_vwPartCountsHistory.CounterTypeId=82/*Cylindrical*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 Group BY dbo_vwParts
 
UNION
 
SELECT p.Displayname,0 AS c1, count (*) AS c1,0 AS c2,0 As c3,0 AS c4,0 As c5,  AS c6,SUM(CounterValue) As c7,0 AS c8,0 AS c9
 
FROM dbo_vwPartCountsHistory AS ch   INNER JOIN dbo_vwParts AS p  ON ch.partid=p.id
where ( dbo_vwPartCountsHistory.CounterTypeId=83/*OverSizel*/) AND (dbo_vwPartCountsHistory.CounterTimeStamp>=#4/25/2014 10:0:0#) AND (dbo_vwPartCountsHistory.CounterTimeStamp<=#4/25/2014 11:0:0#)
 
 Group BY dbo_vwParts.Displayname) AS tbl GROUP BY DisplayName
 
ORDER BY DisplayName;

**al1_24** · 14/05/2014, 16h21

Quand on manipule une requête SQL qui comprend autant d'éléments que celle-ci, il convient d'être extrêmement vigilant sur la syntaxe.
Je te conseillerai de tester chacune des requêtes qui composent ton UNION pour t'assurer qu'elles sont correctes, aussi bien fonctionnellement que syntaxiquement.
N'oublie pas de vérifier que toutes les requêtes qui composent l'UNION retournent le même nombre de colonnes...

**aieeeuuuuu** · 14/05/2014, 16h54

Bonjour,

ligne 31 : SELECTdbo_vwParts.Displayname : il manque une espace !

Indentez votre requete, vous (et nous!) y verrez déjà plus clair

**aieeeuuuuu** · 14/05/2014, 17h04

ligne 57 : 0 AS c5, AS c6 : il manque le 0 (?)

Par ailleurs, vous spécifiez plusieurs fois les même alias C1 dans vos requête en union...

**al1_24** · 14/05/2014, 17h15

... sauf dans la première !
Ce qui fait que les requêtes en UNION n'ont pas toutes le même nombre de colonnes

**facteur** · 15/05/2014, 15h36

Bonjour al1_24, aieeeuuuuu
Merci pour ces infos..je vais les tester et je reviens vers vous

**facteur** · 16/05/2014, 14h30

Bonjour,
Suite à vos remarque , j'ai apporté les modifications à ma requete sql .
Elle fonctionne bien sauf pour les 2 sous requetes ci dessous où la somme affichée est abérrante 1,45207490870997E+257.

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
 SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(CounterValue) AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partid = p.id
 
            where (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
 
            GROUP BY DisplayName
 
UNION
 
 
              SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, Sum(CounterValue) AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p  ON ch.partid=p.id
 
             where (CounterTypeId=83) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
       Group BY DisplayName) tbl GROUP BY DisplayName

J'ai beau chercher l'erreur je ne la trouve pas

Je vous affiche la requete sql dans son intégralité

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
SELECT DisplayName, Sum(c1) AS keyboard, Sum(c2) AS OHScanner, Sum(c3) AS HHScanner, Sum(c4) AS Stray, Sum(c5) AS Fragile, Sum(c6) AS Cylindrical, Sum(c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
 
FROM (SELECT p.DisplayName, COUNT(*) AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5,0 AS c6, 0 As c7,0 AS c8, 0 AS c9
 
                 FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
 
               WHERE (((dbo_vwItemEventHistory.ItemEventTypeID)=5) AND ((dbo_vwItemEventHistory.EventTime)>=#4/25/2014 10:0:0# And (dbo_vwItemEventHistory.EventTime)<=#4/25/2014 11:0:0#) AND ((id.InductionMode)='Keyboard'))
                 AND id.dischargePartId <> 3645
 
               GROUP BY p.DisplayName
 
 
 UNION
 
                SELECT p.DisplayName, 0 AS c1, count (*) AS c2, 0 As c3 ,0 AS c4, 0 As c5, 0 AS c6, 0 As c7 ,0 AS c8, 0 AS c9
 
               FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id on dbo_vwItemEventHistory.itemID = id.itemID) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
              where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='scanner') 
             AND id.dischargePartId <> 3645
 
 
             GROUP BY p.DisplayName
 
 UNION
 
               SELECT p.Displayname, 0 AS c1, 0 AS c2,  count (*) As c3, 0 AS c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
              FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid) INNER JOIN dbo_vwParts as p ON id.inductionPartId = p.id
 
               where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='HHScanner') 
             AND id.dischargePartId <> 3645
 
 
             GROUP BY p.DisplayName
 
 
 UNION
 
            SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 AS c3, count (*) AS  c4, 0 As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
             FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid = id.itemid ) INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='Stray') 
             AND id.dischargePartId <> 3645
 
              GROUP BY p.DisplayName
 
 
UNION
 
           SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, count (*) As c5, 0 AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
           FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.itemid=id.itemid)INNER JOIN dbo_vwParts AS p ON id.inductionPartId = p.id
 
             where (dbo_vwItemEventHistory.ItemEventTypeId=5) AND (dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0#) And (dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#) AND( id.inductionMode='fragile') 
            AND id.dischargePartId <> 3645
 
 
            GROUP BY p.DisplayName
 
 
UNION
 
           SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, Sum(CounterValue) AS c6, 0 As c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p ON ch.partid = p.id
 
            where (CounterTypeId = 82) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
 
            GROUP BY DisplayName
 
UNION
 
 
              SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 As c3, 0 AS c4, 0 As c5, 0 AS c6, Sum(CounterValue) AS c7, 0 AS c8, 0 AS c9
 
            FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts AS p  ON ch.partid=p.id
 
             where (CounterTypeId=83) AND (CounterTimeStamp>=#4/25/2014 10:0:0#) AND (CounterTimeStamp<=#4/25/2014 11:0:0#)
 
       Group BY DisplayName) tbl GROUP BY DisplayName
 
 
ORDER BY DisplayName

**aieeeuuuuu** · 16/05/2014, 15h40

Envoyé par facteur

J'ai beau chercher l'erreur je ne la trouve pas

Alors :
1/ Simplifiez votre requête (vous pouvez fusionner plusieurs requêtes en UNION : les 5 premières ensembles, et les deux dernière ensemble)
2/ indentez là
3/ supprimez les parenthèses inutiles

ce qui donne :

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
 
SELECT DisplayName, Sum(c1) AS keyboard, Sum(c2) AS OHScanner, Sum(c3) AS HHScanner, Sum(c4) AS Stray, Sum(c5) AS Fragile, Sum(c6) AS Cylindrical, Sum(c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS Total
FROM (
 
	SELECT 
		p.DisplayName
		, COUNT(CASE WHEN id.InductionMode ='Keyboard'	THEN 1 END) AS c1
		, COUNT(CASE WHEN id.inductionMode ='scanner'	THEN 1 END) AS c2
		, COUNT(CASE WHEN id.inductionMode ='HHScanner'	THEN 1 END) AS c3
		, COUNT(CASE WHEN id.inductionMode ='Stray'	THEN 1 END) AS c4
		, COUNT(CASE WHEN id.inductionMode ='fragile'	THEN 1 END) AS c5
		,	0 AS c6, 0 AS c7
	FROM dbo_vwItemEventHistory 
	INNER JOIN dbo_vwItemData AS id 
		ON dbo_vwItemEventHistory.ItemID = id.ItemID
	INNER JOIN dbo_vwParts AS p 
		ON id.InductionPartID = p.ID
	WHERE
			dbo_vwItemEventHistory.ItemEventTypeID = 5  
		AND dbo_vwItemEventHistory.EventTime>=#4/25/2014 10:0:0# 
		AND dbo_vwItemEventHistory.EventTime<=#4/25/2014 11:0:0#
		AND id.InductionMode IN ('Keyboard','scanner','HHScanner','Stray')
		AND id.dischargePartId <> 3645
	GROUP BY p.DisplayName
UNION
	SELECT 
		p.Displayname
		, 0 AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 AS c5
		, SUM(CASE WHEN CounterTypeId = 82 THEN CounterValue END) AS c6
		, SUM(CASE WHEN CounterTypeId = 83 THEN CounterValue END) AS c7
 
	FROM dbo_vwPartCountsHistory AS ch 
	INNER JOIN dbo_vwParts AS p 
		ON ch.partid = p.id
	WHERE 
		CounterTypeId IN (82, 83) 
		AND CounterTimeStamp>=#4/25/2014 10:0:0# 
		AND CounterTimeStamp<=#4/25/2014 11:0:0#
	GROUP BY DisplayName
 
) tbl 
GROUP BY DisplayName
ORDER BY DisplayName

Ce qui rend les recherches un peu plus simples et agréables non ?

Je ne vois rien de particulier à première vue dans la dernière sous requête (qui regroupe les deux requêtes qui vous semblent incorrectes)
êtes vous sûr de votre critère de jointures ? un produit cartésien pourrait être à l'origine de ces chiffres élevés.

Que contient CounterValue comme valeurs. Que donne le résultat d'une somme (et d'un MAX) sans faire la jointure ? (et de quelle table provient cette colonne ?)

Pouvez-vous nous fournir la structure des deux tables ?

**facteur** · 19/05/2014, 14h39

Bonjour ,
Au sujet de la simplification des sous-requêtes , il est vrai que c'est beaucoup plus lisible, mais disons que pour l'instant je laisserais ma requête telle que pour que je puisse résoudre le problème de la requête qui m'affiche un résultat aberrant ..

J'essaie de résoudre un problème à la fois..

Au sujet de cette sou- requête qui m'affiche de résultats aberrants, si je l'affiche seule en requête simple), le résultat est correct. C 'est dès que je l’insère dans les requêtes union que le résultat est faux.
Le CounterValue affiche un nombre en général compris entre 1 à 10. Cette colonne provient d'une table où je ne peux pas modifier ( c'est une table liée).

Avec la jointure,
Si je remplace Sum par max, , la valeur qui s'affiche est fausse... Le résultat est 1 au lieu de 3 par exemple.
Si je laisse Sum, le résultat est faux... Il m'affiche

1,45207490870997E+257

.

Sans la jointure, si j'efface

Code :

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

INNER JOIN dbo_vwParts  AS p ON ch.partid = p.id

dans la ligne

Code :

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

FROM dbo_vwPartCountsHistory AS ch INNER JOIN dbo_vwParts  AS p ON ch.partid = p.id

on me demande de rentrer la valeur de paramètre

p.DisplayName

Convertir une requête SQL Server en requête Access

Langage SQL

Discussions similaires

Partager

Partager