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

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

Hi,

You do not need to write cte2 and cte3 in above query,

You need to remove these lines and add other lines

Need to remove lines

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)

Add This line,

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

Your final query looks like,

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Accepted answer
Hi,

You do not need to write cte2 and cte3 in above query,

You need to remove these lines and add other lines

Need to remove lines
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)
Add This line,
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
Your final query looks like,
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