Syntax Errors in 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)

Syntax Errors in Query

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2008-04-03 : 17:18:19

Hi,

I have created a query, but I keep getting 4 errors when I execute it. My query is :

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
,OpenQty
,OpeningBalValue
,ISNULL(RecQty,0.00)
,ISNULL(RecValue,0.00)
,0.00
,0.00
,0.00
,0.00
,ISNULL(AdjQty,0.00)
,ISNULL(AdjValue,0.00)
,AllocQty
,AllocValue
,ClosingQty
,ClosingValue

FROM
(
SELECT
LEFT(I3.LOCNCODE,6) as LocnCode
,LTRIM(STR(YEAR(GETDATE())) + REPLACE(STR(MONTH(GETDATE()), 2),' ', '0')) As ProcessDate
,I2.[CP PRODUCT CODE] as ProductCode
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As OpenQty
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 As OpeningBalValue
,SUM(I3.QTYONHND*I2.[UNITS/CASE]) As ClosingQty
,SUM(I3.QTYONHND*I2.[UNITS/CASE]*I1.CURRCOST)/71.70 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

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 LINE 51
,SUM(I5.EXTDCOST/71.70) as AdjValue
FROM IV30300 I5
RIGHT OUTER JOIN [ITEM_MAPPING] I6
ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR
AND DOCTYPE = '1' 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 JOIN
(
SELECT
LTRIM(STR(YEAR(I12.DOCDATE) + REPLACE(STR(MONTH(I12.DOCDATE), 2),' ', '0')) As ProcessDate12
,I11.[CP PRODUCT CODE] as ProductCode11 LINE 67
,MONTH(I12.DOCDATE) as DocDate11
,SUM(I12.TRXQTY*I11.[UNITS/CASE]) as AllocQty
,SUM(I12.EXTDCOST/71.70) 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
ON D2.ProductCode2 = D12.ProductCode11

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 LINE 88
ON D12.ProductCode2 = D4.ProdCode
LEFT OUTER JOIN
(
SELECT
MONTH(P1.DATERECD) as RecDate
,LTRIM(STR(YEAR(P1.DATERECD) + REPLACE(STR(MONTH(P1.DATERECD), 2),' ', '0')) as ProcessDate7
,I7.[CP PRODUCT CODE] as ProductCode2 LINE 95
,P1.TRXLOCTN as Loc
,SUM(P1.QTYSHPPD * I7.[UNITS/CASE]) as RecQty
,SUM(P1.QTYSHPPD * I7.[CASE PRICE]) as RecValue

FROM POP10500 P1
RIGHT OUTER JOIN [ITEM_MAPPING] I7
ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER]
AND TRXLOCTN = 'MGD_MAIN'
AND P1.DATERECD BETWEEN '12/01/2007' AND '12/31/2007'
GROUP BY I7.[CP PRODUCT CODE], P1.TRXLOCTN, LTRIM(STR(YEAR(P1.DATERECD) + REPLACE(STR(MONTH(P1.DATERECD), 2),' ', '0'))
)D3
ON D4.ProdCode = D3.ProductCode2

The errors returned are :

Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 67
Line 67: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 88
Line 88: Incorrect syntax near 'D4'.
Server: Msg 170, Level 15, State 1, Line 95
Line 95: Incorrect syntax near ','.

Can anyone help me ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-03 : 17:39:48

It is a sure sign of an error when you have 6 ( on a line and only have 5 ).

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-03 : 19:00:58

that is, the parens on this line (and probably other lines) don't match up:

LTRIM(STR(YEAR(I5.DOCDATE) + REPLACE(STR(MONTH(I5.DOCDATE), 2),' ', '0')) As ProcessDate2

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources