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

Question

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

Accepted Answer

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

Answer

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.

Share via

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

1 additional answer

Your answer