Discussion :

[facteur]

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]

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]

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

[facteur]

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]

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]

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]

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]

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.