9 Replies Latest reply on Jan 21, 2014 10:26 AM by Robert Morton

    SQL Query Question - unable to use "with"

    Stephen Wagner

      Does anyone know what I can use in place of "with"?  SQL Server 2005 runs fine with the query below but Tableau gives me this error:

       

      [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'with'.

      [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

      [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'.

       

       

      QUERY:

      with B as (select row_number()over(partition by [Master Number] order by [Master Number])

      as rnum,* from SalesTransactions)

       

      Select [Master Number],

      [Document Date],

      [SOP Number],

      [SOP Type],

      [Customer Class],

      [Originating Subtotal],

      [Originating Trade Discount Amount],

      ([Originating Subtotal] - [Originating Trade Discount Amount]) AS [Demand Amount]

      from B A

       

      where  rnum in

      (SELECT Min(rnum)

      FROM B

      GROUP BY [Master Number]

      having B.[Master Number]=A.[Master Number])

      AND [SOP Type]='Order'

      AND [Customer Class] LIKE 'Retail%'

      AND [Document Date] BETWEEN '10/13/2013' AND '10/26/2013'

      AND [SOP Number] NOT LIKE '%SVC%'