none
Problem with a query

    Question

  •  Hi,

    So this query works fine in SQL express 2012, but in SQL express 2008 I get the following error  Incorrect syntax near 'order'.:

    What am I doing wrong, is driving me mad?

    ;WITH     receipts_rt     AS
    (
         SELECT     Itemnumber
         ,     PurchaseDate
    	 , Cost
         ,     Quantity received_quantity
         ,     SUM (quantity) OVER ( PARTITION BY  ItemNumber ORDER BY PurchaseDate,Purchaseid) AS received_total
         ,     Purchaseid
         FROM    Doc_Purchases
    )
    ,     deliveries_rt     AS
    (
         SELECT   ItemNumber
         ,     SalesDate
         ,     Quantity delivered_quantity
    	 ,DocT
    	 ,quantity qty
         ,SUM (quantity) OVER ( PARTITION BY  ItemNumber ORDER BY SalesDate,SalesID) AS delivered_total
         , ROW_NUMBER () OVER ( PARTITION BY  ItemNumber     
                                ORDER BY   Salesdate   )     AS first_contracted
         ,     SalesID
         FROM    Doc_Sales
    )

    Thx

    Nick

    Friday, November 08, 2013 12:24 AM

All replies

  • Try

    ;WITH     receipts_rt     AS
    (
         SELECT     Itemnumber
         ,     PurchaseDate
    	 , Cost
         ,     Quantity received_quantity
         ,     SUM (quantity) OVER ( PARTITION BY  ItemNumber) AS received_total
         ,     Purchaseid
         FROM    Doc_Purchases
    )
    ,     deliveries_rt     AS
    (
         SELECT   ItemNumber
         ,     SalesDate
         ,     Quantity delivered_quantity
    	 ,DocT
    	 ,quantity qty
         ,SUM (quantity) OVER ( PARTITION BY  ItemNumber ) AS delivered_total
         , ROW_NUMBER () OVER ( PARTITION BY  ItemNumber     
                                ORDER BY   Salesdate   )     AS first_contracted
         ,     SalesID
         FROM    Doc_Sales
    )


    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Saeid Hasani Friday, November 08, 2013 7:06 AM
    Friday, November 08, 2013 1:55 AM
  • Yes, I forgot that SQL 2008 does not allow order by Clause in aggregates which works in 2012 version. I'm worried tough,  if i remove the ordering, that the sequence within the partition will change which will result in wrong information. I'll try this and give an update ...Thanks

    Friday, November 08, 2013 2:00 AM
  • If you are not sure and/or working on older version than you can use Subquery for the SUM

    [Personal Site] [Blog] [Facebook]signature

    Friday, November 08, 2013 3:16 AM
  • Yes, as I thought, syntax above is correct but information obtained form it is wrong. Sequence within partitions is changed and it gives different results.
    Friday, November 08, 2013 11:21 AM
  • have you try using subquery as i mentioned?

    please post DDL+DML and i will help with the specific query?


    [Personal Site] [Blog] [Facebook]signature

    Friday, November 08, 2013 11:27 AM