Incorrect Syntax near With

Question

No matter where I place my With statement inside the SQL query, the keyword in the next line always shows an error, 'Incorrect syntax near keyword'. I also tried putting semi-colon.

; WITH Commercial_subset AS
(
    SELECT DISTINCT
        PRDID_Clean, Value, [Year] 
    FROM
        Reporting_db_SPKPI.DBO.[tbl_RCCP_commercial]  
    WHERE
        MEASURE = 'Unit Growth Rate'
)
--error appears at truncate
TRUNCATE TABLE Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup]

Example 1:

[Example 1][1]

Example 2:

[Example 2][2]

What am I missing? [1]: https://i.stack.imgur.com/lkfVd.png [2]: https://i.stack.imgur.com/tZRnG.png

My Final code after getting suggestions in the comments,

    --Ensure the correct database is selected for creating the views
    USE Reporting_db_SPKPI
    
    --Create the table where new values will be appended
    Insert into Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup]
    Select *, Replace(productID,'-','') as ProductID_clean from  Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR]
    GO
    
    --Create a subset as view which will be used for join later
    Create or Alter View QRY_Commerical_Subset AS
        Select Distinct PRDID_Clean, Value, [Year] From Reporting_db_SPKPI.DBO.[tbl_RCCP_commercial]  where MEASURE = 'Unit Growth Rate'
    Go
    
    --Create a view with distinct list of all SKUs
    CREATE OR ALTER VIEW QRY_RCCP_TEMP AS 
        SELECT 
            PRODUCTID, ROW_NUMBER() Over (ORDER BY ProductID) AS ID 
        FROM (
            SELECT 
                DISTINCT A.ProductID_clean ProductID 
            FROM 
                Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup] A 
            LEFT JOIN 
                Reporting_db_SPKPI.DBO.QRY_Commerical_Subset B ON A.ProductID_clean = B.PRDID_Clean 
            WHERE 
                B.PRDID_Clean IS NOT NULL --and A.filename = 'Capacity Planning_INS_Springhill'  --DYNAMIC VARIABLE HERE
                and Cast(A.SnapshotDate as date) = 
                    (SELECT Max(Cast(SnapshotDate as date)) FROM reporting_db_spkpi.dbo.tbl_RCCP_3_NR)
        ) T
    GO
    
    SET NOCOUNT ON
    
    -- For every product id from the distinct list iterate the following the code
    DECLARE @I INT = 1
    WHILE @I <= (SELECT MAX(ID) FROM QRY_RCCP_TEMP)
    BEGIN
    
        DECLARE @PRODUCT NVARCHAR(50) = (SELECT PRODUCTID FROM QRY_RCCP_TEMP WHERE ID = @I)
        DROP TABLE Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_temp]
        
        --Retrieve last 12 months of value from NR and add it to a temp table in increasing order of their months. These 12 data points will be baseline
        SELECT 
            Top 12 A.*, 
            Case When B.[Value] is Null then 0 else CAST(B.[Value] as float) End GROWTH
        INTO 
            Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_temp]
        FROM 
            Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup] A
        LEFT JOIN 
            --using the view here
            QRY_Commerical_Subset B ON B.PRDID_Clean = A.ProductID_clean AND B.[YEAR] = YEAR(A.[MONTH])+1 
        WHERE 
            A.PRODUCTID= @PRODUCT
            AND Cast(A.SnapshotDate AS DATE) = (SELECT Max(Cast(SnapshotDate AS DATE)) FROM reporting_db_spkpi.dbo.[tbl_RCCP_3_NR_dup])
        Order by 
            [Month] desc
    
        -- Generate 3 years of data
        DECLARE @J INT = 1
        WHILE @J<=3
        BEGIN
            --Calculate next year's value
            UPDATE Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_temp]
            SET 
                [Value] = [Value]*(1+ GROWTH), 
                [MONTH] = DATEADD(YEAR,1,[Month]),
                MonthCode= 'F' + CAST(CAST(SUBSTRING(MonthCode,2,LEN(MonthCode)) AS INT) + 12 AS NVARCHAR(10))
            
            --Add it to the NR table.
            Insert into Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup]
                (ProductID, MonthCode, Value, Month, FileName, 
                LastModifiedDate, SnapshotDate, Quarter, IsError, ErrorDescription)
            Select 
                ProductID, MonthCode, Value, Month, FileName, 
                LastModifiedDate, SnapshotDate, Quarter, IsError, ErrorDescription
            from 
                Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_temp]
    
            --Update growth rate for next year
            UPDATE Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_temp] 
            SET GROWTH = Case When B.[Value] is Null then 0 else CAST(B.[Value] as float) End 
            FROM Reporting_db_SPKPI.DBO.QRY_Commerical_Subset B 
            WHERE B.PRDID_Clean = ProductID_clean AND [YEAR] = YEAR([MONTH])+1
    
            SET @J=@J+1
        END
        SET @I=@I+1
    END
    DROP VIEW QRY_RCCP_TEMP
    DROP VIEW QRY_Commerical_Subset

Answer 1

The WITH is a Common Table Expression, aka CTE.

And a CTE is like a template for a sub-query.

For example this join of the same sub-query:

SELECT *
FROM (
    select distinct bar 
    from table1
    where foo = 'baz'
) AS foo1
JOIN (
    select distinct bar 
    from table1
    where foo = 'baz'
) AS foo2
  ON foo1.bar > foo2.bar

Can be written as

WITH CTE_FOO AS (
  select distinct bar 
  from table1 
  where foo = 'baz' 
) 
SELECT *
FROM CTE_FOO AS foo1
JOIN CTE_FOO AS foo2
  ON foo1.bar > foo2.bar

It's meant to be used with a SELECT.
Not with a TRUNCATE TABLE or DROP TABLE.
(It can be used with an UPDATE though)

As such, treat the TRUNCATE as a seperate statement.

TRUNCATE TABLE Reporting_db_SPKPI.DBO.[tbl_RCCP_3_NR_dup];

WITH Commercial_subset AS
(
    SELECT DISTINCT
        PRDID_Clean, Value, [Year] 
    FROM
        Reporting_db_SPKPI.DBO.[tbl_RCCP_commercial]  
    WHERE
        MEASURE = 'Unit Growth Rate'
)
SELECT *
FROM Commercial_subset;

Btw, the reason why many write a CTE with a leading ; is because the WITH clause raises an error if the previous statement wasn't ended with a ;. It's just a small trick to avoid that error.