Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'order'.

Analyst_SQL 3,531 Reputation points
2020-09-19T08:53:39.527+00:00

HI @EchoLiu-MSFT ,

When i am executing below query in sql 2008 then it giving below error,

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'order'.

,But when i am executing in sql 2014 it is working fine,with same data ,what could be the issue.

WITH CTE  
 AS(SELECT     isnull(CustomerItem.CIName,ItemMasterFile.Descriptionitem) as Descriptionitem,sum(tbl_PckDetail.QTY) as QY,  
  (isnull(Probale.PBSize,itemmasterfile.weight)) as WT,(sum(tbl_PckDetail.QTY)*isnull(Probale.PBSize,  
  itemmasterfile.weight))as Total_wt,('x '+ ''+' '+ ''+ ItemMasterFile.BaleSize) ITEM,  
          
  CAST((isnull(nullif(isnull(Probale.PBSize,itemmasterfile.weight),0)*sum(tbl_PckDetail.QTY)/nullif(2.2046,0),0))  as decimal(18,0)) Kg  
  FROM         tbl_SBDispatachD  
  inner join tbl_PckDetail on tbl_PckDetail.PID=tbl_SBDispatachD.PID  
  inner join ItemMasterFile on ItemMasterFile.CodeItem=tbl_PckDetail.Codeitem  
          
  inner join   SalesOrder ON SalesOrder.OrderNo = tbl_PckDetail.OrderNo   
     left join CustomerItem on CustomerItem.Codeitem=tbl_PckDetail.Codeitem   
                        and CustomerItem.CustomerID=SalesOrder.Customerid  
          
          
    left outer join Probale on Probale.prdno=tbl_PckDetail.Prdno  
     and ItemMasterFile.CodeItem=Probale.codeitem  
          
  where tbl_SBDispatachD.ID =1109 and tbl_PckDetail.DelID is null and tbl_SBDispatachD.Del is null   
  group by  CustomerItem.CIName,ItemMasterFile.Descriptionitem ,Probale.Pbsize,ItemMasterFile.weight ,ItemMasterFile.BaleSize)  
      
 ,cte2 as(SELECT Descriptionitem,SUM (QY) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) QY,SUM (WT) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)WT,  
        SUM (Total_wt) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) Total_wt,Item,SUM (kg) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)kg  
        FROM CTE)    
 ,cte3 as(select *,row_number() over(partition by Descriptionitem order by Descriptionitem)rn from cte2)  
      
 select * from cte3 where rn<2  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,225 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,513 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 109.8K Reputation points
    2020-09-19T12:52:55.85+00:00

    Check if the next query works in SQL Server 2008 and gives the expected results:

    WITH CTE AS
    (
    SELECT     isnull(CustomerItem.CIName,ItemMasterFile.Descriptionitem) as Descriptionitem,sum(tbl_PckDetail.QTY) as QY,
       (isnull(Probale.PBSize,itemmasterfile.weight)) as WT,(sum(tbl_PckDetail.QTY)*isnull(Probale.PBSize,
       itemmasterfile.weight))as Total_wt, 'x '+ ''+' '+ ''+ ItemMasterFile.BaleSize as X,
    
      CAST((isnull(nullif(isnull(Probale.PBSize,itemmasterfile.weight),0)*sum(tbl_PckDetail.QTY)/nullif(2.2046,0),0))  as decimal(18,0)) Kg
       FROM         tbl_SBDispatachD
       inner join tbl_PckDetail on tbl_PckDetail.PID=tbl_SBDispatachD.PID
       inner join ItemMasterFile on ItemMasterFile.CodeItem=tbl_PckDetail.Codeitem
    
       inner join   SalesOrder ON SalesOrder.OrderNo = tbl_PckDetail.OrderNo 
          left join CustomerItem on CustomerItem.Codeitem=tbl_PckDetail.Codeitem 
                             and CustomerItem.CustomerID=SalesOrder.Customerid
    
    
         left outer join Probale on Probale.prdno=tbl_PckDetail.Prdno
          and ItemMasterFile.CodeItem=Probale.codeitem
    
       where tbl_SBDispatachD.ID =1109 and tbl_PckDetail.DelID is null and tbl_SBDispatachD.Del is null 
       group by  CustomerItem.CIName,ItemMasterFile.Descriptionitem ,Probale.Pbsize,ItemMasterFile.weight ,ItemMasterFile.BaleSize)
    )
    select Descriptionitem, SUM(QY) as QY, SUM(WT) as WT, SUM(Total) as Total, MAX(X), SUM(Kg) as Kg
     from CTE
     group by Descriptionitem
    
    2 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 98,751 Reputation points
    2020-09-19T09:03:11.147+00:00

    This is the problematic part:

    SUM (QY) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)
    

    The ability to specify an ORDER BY clause in an OVER clause to permit efficient calculations of running rums was introduced in SQL 2012, and is thus not available on SQL 2008.

    There are other ways to express running sum which uses syntax supported in SQL 2008. However, these result in really poor performance. And given that SQL 2008 is at end of life, I think you should insist on an upgrade rather than wasting time on rewriting the code.

    1 person found this answer helpful.