Referencing a sub-query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Referencing a sub-query

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-09-09 : 13:29:39

Hi,

How do I reference a value in a sub-query ? I have written this query :

INSERT INTO INVENTRY([STOCKLIST CODE], [WAREHOUSE CODE], [PROCESS YYYYMM], [INVENTORY TYPE], [PRODUCT CODE], [OPENING BALANCE QTY.], [OPENING BAL. VALUE],[RECEIPT QUANTITY],[RECEIPT VALUE],[FREE RECEIPT QUANTITY],[FREE RECEIPT VALUE],[RETURN QUANTITY],[RETURN VALUE],[ADJUSTMENT QUANTITY],[ADJUSTMENT VALUE],[ALLOCATED QUANTITY],[ALLOCATED VALUE],[CLOSING QUANTITY],[CLOSING VALUE])

SELECT '5555550101'
,LocnCode
,ProcessDate
,'S'
,ProductCode
,ISNULL(OpenQty,0.00)
,ISNULL(OpeningBalValue,0.00)
,ISNULL(RecQty,0.00)
,ISNULL(RecValue,0.00)
,0.00
,0.00
,ISNULL(RetQty,0.00)
,ISNULL(RetValue,0.00)
,ISNULL(AdjQty,0.00)
,ISNULL(AdjValue,0.00)
,ISNULL(AllocQty,0.00)
,ISNULL(AllocValue,0.00)
,ISNULL(OpenQty,0.00)+ISNULL(RecQty,0.00)+ISNULL(RetQty,0.00)+ISNULL(AdjQty,0.00)-ISNULL(AllocQty,0.00)
,ISNULL(OpeningBalValue,0.00)+ISNULL(RecValue,0.00)+ISNULL(RetValue,0.00)+ISNULL(AdjValue,0.00)-ISNULL(AllocValue,0.00)

FROM
(

SELECT
LTRIM(STR(YEAR(I12.DOCDATE)) + REPLACE(STR(MONTH(I12.DOCDATE), 2),' ', '0')) As ProcessDate12
,I11.[CP PRODUCT CODE] as ProductCode11
,SUM(I12.TRXQTY*I11.[UNITS/CASE]*-1) as AllocQty
,SUM(I12.TRXQTY*I11.[UNITS/CASE]*I11.[UNIT PRICE]*-1) as AllocValue
FROM IV30300 I12
RIGHT OUTER JOIN [ITEM_MAPPING] I11
ON I11.[KIRK ITEM NUMBER] = I12.ITEMNMBR
and I12.DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
AND I12.DOCTYPE = '6'
GROUP BY I11.[CP PRODUCT CODE], LTRIM(STR(YEAR(I12.DOCDATE)) + REPLACE(STR(MONTH(I12.DOCDATE), 2),' ', '0'))
)D12
RIGHT OUTER JOIN
(
SELECT
LEFT(I3.LOCNCODE,6) as LocnCode
,'200712' As ProcessDate
,I2.[CP PRODUCT CODE] as ProductCode
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As ClosingQty1
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/67 As ClosingValue

FROM IV00101 I1
JOIN [ITEM_MAPPING] I2
ON I1.ITEMNMBR = I2.[KIRK ITEM NUMBER]
JOIN IV00102 I3
ON I1.ITEMNMBR = I3.ITEMNMBR
AND I3.LOCNCODE = 'MGD_MAIN'

GROUP BY I2.[CP PRODUCT CODE], LEFT(I3.LOCNCODE,6)
)D1
ON D12.ProductCode11 = D1.ProductCode
LEFT JOIN
(
SELECT
B2.[CP PRODUCT CODE] as Item
,SUM(B1.[QTY PER STOCK]*B2.[UNITS/CASE]) as ClosingQty
,SUM(B1.[QTY PER STOCK]*B2.[UNITS/CASE]) as OpenQty
,SUM(B1.[CURRENT COST]*(B1.[QTY PER STOCK]*B2.[UNITS/CASE])) As OpeningBalValue
FROM OPENINGBAL B1
INNER JOIN [ITEM_MAPPING] B2
ON B1.[ITEM NUMBER] = B2.[KIRK ITEM NUMBER]
GROUP BY B2.[CP PRODUCT CODE]
)D13
ON D1.ProductCode = D13.Item

LEFT JOIN
(
SELECT
I6.[CP PRODUCT CODE] as ProductCode2
,LTRIM(STR(YEAR(I5.DOCDATE)) + REPLACE(STR(MONTH(I5.DOCDATE), 2),' ', '0')) As ProcessDate2
,SUM(I5.TRXQTY*I6.[UNITS/CASE]) as AdjQty
,SUM(I6.[UNIT PRICE]*(I5.TRXQTY*I6.[UNITS/CASE])) as AdjValue
FROM IV30300 I5
RIGHT OUTER JOIN [ITEM_MAPPING] I6
ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR
AND DOCTYPE = '1'
AND DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
or DOCTYPE = '2'
AND DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'

GROUP BY I6.[CP PRODUCT CODE],LTRIM(STR(YEAR(I5.DOCDATE)) + REPLACE(STR(MONTH(I5.DOCDATE), 2),' ', '0'))
)D2
ON D1.ProductCode = D2.ProductCode2
and D1.ProcessDate = D2.ProcessDate2

LEFT OUTER JOIN
(
SELECT
I9.[CP PRODUCT CODE] as ProdCode
FROM [ITEM_MAPPING] I9
LEFT OUTER JOIN IV30300 I10
ON I9.[KIRK ITEM NUMBER] = I10.ITEMNMBR
GROUP BY I9.[CP PRODUCT CODE]
)D4
ON D12.ProductCode11 = D4.ProdCode
LEFT OUTER JOIN
(
SELECT
LTRIM(STR(YEAR(P1.DOCDATE)) + REPLACE(STR(MONTH(P1.DOCDATE), 2),' ', '0')) as ProcessDate7
,I7.[CP PRODUCT CODE] as ProductCode8
,P1.TRXLOCTN as Loc
,SUM(P1.TRXQTY * I7.[UNITS/CASE]) as RecQty
,SUM(I7.[UNIT PRICE]*(P1.TRXQTY * I7.[UNITS/CASE])) as RecValue

FROM IV30300 P1
RIGHT OUTER JOIN [ITEM_MAPPING] I7
ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER]
AND P1.DOCTYPE = '4'
AND TRXLOCTN = 'MGD_MAIN'
AND P1.DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
GROUP BY I7.[CP PRODUCT CODE], P1.TRXLOCTN, LTRIM(STR(YEAR(P1.DOCDATE)) + REPLACE(STR(MONTH(P1.DOCDATE), 2),' ', '0'))
)D3
ON D4.ProdCode = D3.ProductCode8
LEFT OUTER JOIN
(
SELECT
LTRIM(STR(YEAR(P10.DOCDATE)) + REPLACE(STR(MONTH(P10.DOCDATE), 2),' ', '0')) as ProcessDate7
,I70.[CP PRODUCT CODE] as ProductCode10
,P10.TRXLOCTN as Loc
,SUM(P10.TRXQTY * I70.[UNITS/CASE]) as RetQty
,SUM(I70.[UNIT PRICE]*(P10.TRXQTY * I70.[UNITS/CASE])) as RetValue

FROM IV30300 P10
RIGHT OUTER JOIN [ITEM_MAPPING] I70
ON P10.ITEMNMBR = I70.[KIRK ITEM NUMBER]
AND P10.DOCTYPE = '5'
AND TRXLOCTN = 'MGD_MAIN'
AND P10.DOCDATE BETWEEN '12/01/2007' AND '12/31/2007'
GROUP BY I70.[CP PRODUCT CODE], P10.TRXLOCTN, LTRIM(STR(YEAR(P10.DOCDATE)) + REPLACE(STR(MONTH(P10.DOCDATE), 2),' ', '0'))
)D10
ON D4.ProdCode = D10.ProductCode10
WHERE OpenQty > 0 or RetQty > 0 or AdjQty > 0 or RecQty > 0

I would like to pass the value of RecQty which is in D3 to add it to my ClosingQty which is in D13. I have tried using D3.RecQty but I get an error saying that D3 is not properly defined.

What is the proper syntax for this ?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources