消息102，级别15，状态1，行28''order'附近的语法不正确。 [英] Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'order'.

问题描述

我尝试了以下脚本 

我收到错误 

消息102，等级15，State 1，Line 28

'order'附近的语法不正确。

   与

        grouped_by_date AS

    （

     SELECT

       [Sales_Organization]，

       [Market_Grp]，

       [Delivery_Year]，

        [Delivery_Month]，

       [Invoicing_Day]，

        SUM（QTY_UoM）        AS重量

     FROM

       tmp.factsales s

     GROUP BY

    ;    [SALES_ORGANIZATION]，

       [Market_Grp]，

   &NBSP ;   [Delivery_Year]，&
       [Delivery_Month]，

      ;& NBSP; [Invoicing_Day]

    ），
        cumulative_sum_for_ytd AS

    （

     SELECT

       *，

       

      

       SUM（[重量]）OVER（PARTITION BY [Delivery_Year] ORDER BY [Delivery_Month]，[Invoicing_Day]

                &NBSP）

              &NBSP ;   AS Weight_YTD

     FROM

       grouped_by_date

  ），



$
        hack_to_do_lag AS

    （

     SELECT

       *，

        CASE

         WHEN [Delivery_Year]％2 = 1

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; THEN MAX（CASE WHEN [Delivery_Year]％2 = 0 THEN [Weight_YTD] END）OVER（PARTITION BY（[Delivery_Year] 0）/ 2）<登记/>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ELSE MAX（CASE WHEN [Delivery_Year]％2 = 1 THEN [Weight_YTD] END）OVER（PARTITION BY（[Delivery_Year] + 1）/ 2）

       END

         AS Weight_PreviousYTD

&NBSP;&NBSP;&NBSP;&NBSP;从

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cumulative_sum_for_ytd

&NBSP;  ）

  &NBSP;选择

  &NBSP; &NBSP; &NBSP; *

  &NBSP;来自
  &NBSP; &NBSP; &NBSP; hack_to_do_lag

解决方案

如果分区依据与您的分组相同，则应该只能删除分区。

你不能使用  SUM 的  with  OVER  和  通过分区
。订购 ，仅限  PARTITION
BY 。订购你要求的东西是没有意义的。

I tried the following script 

I got error 

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

    WITH
        grouped_by_date AS
    (
        SELECT
            [Sales_Organization],
            [Market_Grp],
            [Delivery_Year],
            [Delivery_Month],
            [Invoicing_Day],
            SUM(QTY_UoM)             AS Weight
        FROM
            tmp.factsales s
        GROUP BY
            [Sales_Organization],
            [Market_Grp],
            [Delivery_Year],
            [Delivery_Month],
            [Invoicing_Day]
    ),
        cumulative_sum_for_ytd AS
    (
        SELECT
            *,
           
           
            SUM([Weight]) OVER (PARTITION BY [Delivery_Year] ORDER BY [Delivery_Month], [Invoicing_Day]
                               )
                                  AS Weight_YTD
        FROM
            grouped_by_date
    ),


        hack_to_do_lag AS
    (
        SELECT
            *,
            CASE
                WHEN [Delivery_Year]%2=1
                THEN MAX(CASE WHEN [Delivery_Year]%2=0 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+0)/2)
                ELSE MAX(CASE WHEN [Delivery_Year]%2=1 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+1)/2)
            END
                AS Weight_PreviousYTD
        FROM
            cumulative_sum_for_ytd
    )
    SELECT
        *
    FROM
        hack_to_do_lag

解决方案

If the partition by is the same as the group by you should be able to just drop the partition by.

You cannot use SUM with OVER and Partition by..Orderby, only with PARTITION BY. It doesnt make sense to order something that you want to sum.

这篇关于消息102，级别15，状态1，行28''order'附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！