I'm adapting the fifo-stock-inventory-sql-problem for our database but all 3 NONCLUSTERED INDEX portions of SQL code appear to error with Incorrect syntax near '(' [ i.e. I get 3 errors, all Incorrect syntax near '(' ]. What do I need to change to eliminate the error?
Earlier I changed WITH to ;WITH (i.e. added ; a prefix to WITH). Commenting out 1 of the 3 NONCLUSTERED INDEX portions results in the SQL error dropping from 3 occurrences to 2. Working with SQL Server 2016 (130) database.
PS: Field [TRANSTYPE] is Integer type (e.g. value of 1 for Receipt)
--- from https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/
---
CREATE NONCLUSTERED INDEX [IX_Dave_General]
ON [dbo].[ICIVAL] --- RotoID IC0352 --- was [dbo].[Stock]
(
[ITEMNO] ASC, --- ascending --- was [ArticleID]
[TRANSDATE] DESC, --- descending --- was [TranDate]
[TRANSTYPE] ASC --- ascending --- was [TranCode]
)
INCLUDE ( [QUANTITY], [TRANSCOST] ) --- was ( [Items], [Price]) --- XXX Price XXX ---
;WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX [IX_Dave_Items]
ON [dbo].[ICIVAL] --- was [dbo].[Stock]
(
[ITEMNO] ASC, --- was [ArticleID]
[TRANSDATE] ASC --- was [TranDate]
)
INCLUDE ( [QUANTITY]) --- was [Items]
WHERE ( ([TRANSTYPE] = 1) ) --- OR ([TRANSTYPE] = 5) ) --- 1 = Receipt, 2 = Rec Adjust, 3 = Rec Return, 5= Shipment Return --- was ([TranCode] IN ('IN', 'RET'))
;WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX [IX_Dave_Price]
ON [dbo].[ICIVAL] --- was [dbo].[Stock]
(
[ITEMNO] ASC, --- was [ArticleID]
[TRANSDATE] ASC --- was [TranDate]
)
INCLUDE ( [TRANSCOST]) --- was ( [Price]) --- XXX Price XXX ---
WHERE ( [TRANSTYPE] = 1 ) --- was ([TranCode]='IN') --- 1 = Receipt, 99 = Dummy
;WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
ON [PRIMARY]
GO
---------------------------------------------------------------------------------------------------------------------------------
--- Sum up the ins and outs to calculate the remaining stock level
;WITH
cteStockSum
AS ( SELECT [ITEMNO] , --- was ArticleID without [ ] brackets
SUM(CASE WHEN [TRANSTYPE] = 5 THEN 0-[QUANTITY] --- was WHEN TranCode = 'OUT' THEN 0 - Items --- TRANSTYPE = 5 means Shipment Returns XXXXX should be Shipment
ELSE [QUANTITY] --- was ELSE Items
END) AS TotalStock
FROM [dbo].[ICIVAL] --- was dbo.Stock
GROUP BY [ITEMNO] --- was GROUP BY ArticleID
),