Discussion :

[fred_vannes56]

Bonjour à tous.

Je souhaite réaliser un état à partir de cette requête:

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
PARAMETERS [A telle date] DateTime;
SELECT Org.companyName AS Frigo, PrDesc.UID AS RefPrd, Prd.UID AS Lot, Prd.dateOfPurchase AS [Date d'entrée], PrDesc.productName AS nom, Prd.qtyNet AS KgEntrés, Prd.cartons AS [Cartons entrés],
 
 (SELECT sum(pr2.qtyNet) FROM Products AS pr2, Contracts AS ctr2 WHERE ((pr2.ledger="Sale") and (ctr2.contractNumber=pr2.contractAssignment) and (pr2.coldstore=[entrepot]) and (ctr2.ETL<=[A telle date])  and (pr2.parentPurchase=prd.UID))  Group by pr2.parentPurchase  ) AS KgSortis,
 
 (SELECT sum(pr2.cartons) FROM Products AS pr2, Contracts AS ctr2 WHERE ((pr2.ledger="Sale") and (ctr2.contractNumber=pr2.contractAssignment) and (pr2.coldstore=[entrepot]) and (ctr2.ETL<=[A telle date])  and (pr2.parentPurchase=prd.UID))  Group by pr2.parentPurchase  ) AS [Cartons sortis], 
 
[Kgentrés]-(IIf(IsNull([kgsortis]),0,[kgsortis])) AS StockKg,
 
 [cartons entrés]-(IIf(IsNull([Cartons sortis]),0,[Cartons sortis])) AS StockCartons,
 
 [Stockkg]*CDbl(prd.price) AS Valeur
 
FROM Organisations AS Org INNER JOIN (ProductDescriptions AS PrDesc INNER JOIN (Products AS Prd INNER JOIN Contracts AS Ctr ON Prd.contractAssignment = Ctr.contractNumber) ON PrDesc.UID = Prd.nameDescription) ON Org.UID = Prd.coldstore
WHERE (((Prd.dateOfPurchase)<=[A telle date]) AND ((Prd.coldstore)=[entrepot]) AND ((Prd.ledger)="purchase"))
ORDER BY PrDesc.productName;

Le problème, c'est que lorsque je termine sa création avec l'assistant, j'obtiens le message d'erreur suivant : Clause GROUP BY multi niveaux non autorisée dans une sous-requête" ...

Y a-t-il une solution?

Merci
Fred

[fred_vannes56]

pas de solution? y a t il un moyen de contourner ce probleme?