SQL Query - Incorrect syntax near '(' - Syntax for CREATE NONCLUSTERED INDEX and associated WITH

161 Views Asked by At

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
     ),
0

There are 0 best solutions below