Problème de requête sous MsQuery

Version imprimable

Bonjour,
J'ai réalisé une requête sql sous SqlServer qui compile et me retourne ce que je veux:
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 SELECT PAR_CODE AS Ref ,SUM(tmp.QtyStockHR) ,SUM(tmp.QtyEnComHR) ,SUM(tmp.QtyStockR) ,SUM(tmp.QtyEnComR) FROM GMAO_PROD.dbo.R5PARTS left outer join( (SELECT DISTINCT PAR_CODE AS Ref ,SUM(ISNULL(RSNR.STO_QTY,0)) AS 'QtyStockHR' ,0 AS 'QtyEnComHR' ,SUM(ISNULL(RSR.STO_QTY,0)) AS 'QtyStockR' ,0 AS 'QtyEnComR' FROM GMAO_PROD.dbo.R5PARTS left outer join R5STOCK RSNR on RSNR.STO_PART = PAR_CODE AND(RSNR.STO_STORE In ('M1-D','M1-E','M3-E','M3-D','MS-E')) left outer join R5STOCK RSR on RSR.STO_PART = PAR_CODE AND(RSR.STO_STORE In ('MR-D','MR-E','MR-F','MT-F')) GROUP BY PAR_CODE) UNION ALL (SELECT DISTINCT PAR_CODE AS Ref ,0 AS 'QtyStockHR' ,SUM(ISNULL(ODHR.ONO_QTY,0)) AS 'QtyEnComHR' ,0 AS 'QtyStockR' ,SUM(ISNULL(ODR.ONO_QTY,0)) AS 'QtyEnComR' FROM GMAO_PROD.dbo.R5PARTS left outer join R5ONORDER ODHR on ODHR.ONO_PART = PAR_CODE AND (ODHR.ONO_STORE In ('M1-D','M1-E','M3-E','M3-D','MS-E')) left outer join R5ONORDER ODR on ODR.ONO_PART = PAR_CODE AND (ODR.ONO_STORE In ('MR-D','MR-E','MR-F','MT-F')) GROUP BY PAR_CODE)) AS tmp ON tmp.ref = PAR_CODE Group by PAR_CODE--,tmp.QtyStockHR ,tmp.QtyStockR ,tmp.QtyEnComHR ,tmp.QtyEnComR ORDER BY PAR_CODE
C'est une requête qui contient 3 jointure dont l'une imbrique les 2 autres...
Le problème est que lorsque je veux qexecuter cette requête dans le compilateur de MSQuery il me retourne des erreurs que je n'arrive pas à résoudre :

Citation:

No column was specified for column 2 of'tmp'
No column was specified for column 3 of'tmp'
No column was specified for column 4 of'tmp'
No column was specified for column 5 of'tmp'
Invalid column name'ref'
Invalid column name 'QtyStockHR'
Invalid column name 'QtyEnComHR'
Invalid column name 'QtyStockR'
Invalid column name 'QtyEnComR'

Svp aidez-moi je suis bloqué.

08/07/2013, 16h37
darkelend

Il faut que tu nommes tes colonnes calculées dans la requête.
Par exemple

Code:

SUM(tmp.QtyStockHR)

devient

Code:

SUM(tmp.QtyStockHR) as sumQtyStockHR
08/07/2013, 17h29
shaddus

Merci beaucoups pour m'avoir répondu d'une part et j'ai donc nommé les colonnes comme dans la requête :
SELECT PAR_CODE AS Ref
,SUM(tmp.QtyStockHR) AS sumQtyStockHR
,SUM(tmp.QtyEnComHR) AS sumQtyEnComHR
,SUM(tmp.QtyStockR) AS sumQtyStockR
,SUM(tmp.QtyEnComR) AS sumQtyEnComR
....
mais ça ne marche toujours pas, ça me retourne toujours cette même erreur:
No column was specified for column 2 of'tmp'
No column was specified for column 3 of'tmp'
No column was specified for column 4 of'tmp'
No column was specified for column 5 of'tmp'
Invalid column name'ref'
Invalid column name 'QtyStockHR'
Invalid column name 'QtyEnComHR'
Invalid column name 'QtyStockR'
Invalid column name 'QtyEnComR'

ah désolé :aie:
Essaye comme ceci, si ca ne fonctionne pas fournie le DDL des tables pour que je puisse tester parce que je fais ca à l'aveugle

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
37
SELECT par_code as ref 
	, SUM (tmp.qtystockhr) 
	, SUM (tmp.qtyencomhr) 
	, SUM (tmp.qtystockr) 
	, SUM (tmp.qtyencomr) 
FROM gmao_prod.dbo.r5parts 
	LEFT outer JOIN (
				SELECT distinct par_code as ref 
					, SUM (ISNULL (rsnr.sto_qty , 0)) as 'qtystockhr' 
					, 0 as 'qtyencomhr' 
					, SUM (ISNULL (rsr.sto_qty , 0)) as 'qtystockr' 
					, 0 as 'qtyencomr' 
				FROM gmao_prod.dbo.r5parts 
					LEFT outer JOIN r5stock rsnr ON rsnr.sto_part = par_code 
					AND (rsnr.sto_store IN ('m1-d' , 'm1-e' , 'm3-e' , 'm3-d' , 'ms-e')) 
					LEFT outer JOIN r5stock rsr ON rsr.sto_part = par_code 
					AND (rsr.sto_store IN ('mr-d' , 'mr-e' , 'mr-f' , 'mt-f')) 
				GROUP by par_code
		UNION all 
				SELECT distinct par_code as ref 
					, 0 as 'qtystockhr' 
					, SUM (ISNULL (odhr.ono_qty , 0)) as 'qtyencomhr' 
					, 0 as 'qtystockr' 
					, SUM (ISNULL (odr.ono_qty , 0)) as 'qtyencomr' 
				FROM gmao_prod.dbo.r5parts 
					LEFT outer JOIN r5onorder odhr ON odhr.ono_part = par_code 
					AND (odhr.ono_store IN ('m1-d' , 'm1-e' , 'm3-e' , 'm3-d' , 'ms-e')) 
					LEFT outer JOIN r5onorder odr ON odr.ono_part = par_code 
					AND (odr.ono_store IN ('mr-d' , 'mr-e' , 'mr-f' , 'mt-f')) 
				GROUP by par_code) as tmp ON tmp.ref = par_code 
GROUP by par_code 
/* 
	, tmp.qtystockhr 
	, tmp.qtystockr 
	, tmp.qtyencomhr 
	, tmp.qtyencomr*/ 
ORDER by par_code