CODEWITHSUNDEEP
sql-serversql-server-2008
Cool Dev
Cool Dev

Reputation: 19

SQL Server 2008 R2 syntax error

Could somebody please tell me why this is causing a syntax error in SQL Server 2008 R2? It works fine in SQL Server 2014.

SELECT        
    CustomerId, RequestDate, 
    SUM(InitialActualCount + (KeyCardCount * x.Activity)) 
       OVER (PARTITION BY CustomerId  ORDER BY RowNumber) AS TotalActualCount,
    RowNumber
FROM            
    (SELECT        
        *, 
        ROW_NUMBER() OVER (PARTITION BY CustomerId
                      ORDER BY CustomerID, RequestDate) AS RowNumber
     FROM            
        RequestTable) x

Table structure: 

 RequestTable (CustomerId, RequestDate,  KeyCardCount, Activity, InitialActualCount)

Error:

Incorrect syntax near 'order'.
Incorrect syntax near 'x'.

Thank you in advance for any help!

Upvotes: 0

Views: 122

Answers (3)

Steven
Steven

Reputation: 15258

SELECT        
   *, 
   ROW_NUMBER() OVER (PARTITION BY CustomerId
                      ORDER BY CustomerID, RequestDate) AS RowNumber
FROM RequestTable

Don't you need an alias for using * ?

SELECT        
   a.*, 
   ROW_NUMBER() OVER (PARTITION BY CustomerId
                      ORDER BY CustomerID, RequestDate) AS RowNumber
FROM RequestTable a

Upvotes: 0

JohnS
JohnS

Reputation: 2052

Lose the "x" as per:

 SELECT
  CustomerId,
  RequestDate,
  SUM(InitialActualCount + ( KeyCardCount * x.Activity )) OVER ( PARTITION BY CustomerId ORDER BY RowNumber ) AS TotalActualCount,
  RowNumber
 FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY CustomerID, RequestDate ) AS RowNumber
    FROM
      RequestTable
  ) RequestTable ( CustomerId, RequestDate, KeyCardCount, Activity,
                   InitialActualCount )

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39566

At least one of your issues is this line:

SELECT /*snip*/ sum(InitialActualCount + (KeyCardCount * x.Activity))
  OVER (partition BY CustomerId  ORDER BY RowNumber) AS TotalActualCount /*snip*/

The ability to add an ORDER BY clause in an OVER clause with an aggregate (SUM in you case) was added in SQL Server 2012.

Getting running totals was a well known problem before 2012 - see Best approaches for running totals – updated for SQL Server 2012 for an exhaustive discussion of this.

Upvotes: 1

Related Questions