动态order by子句的运行时错误(静态等效子句有效)

Question

问题是，除了abap中的硬编码order by子句之外，是否还有其他针对动态order by子句触发的检查。

我在abap中写了一个sql语句。本声明总结了特定年份的参赛作品数量。然后，它根据条目的类型对计数进行分组。之后，我尝试进行动态排序。由于某种原因，这导致了一个错误："解析器产生了错误：包含IN_YEAR的表达式不是GROUP-BY表达式。">

问题是，在对排序条件进行硬编码时，orderby子句是有效的。当我尝试动态地做它时，它不会。

但我必须动态地进行排序，因为稍后排序标准将通过oData(it_order)来自前端。

DATA:    lv_cYear      TYPE NUMC4,
lv_lYear      TYPE NUMC4,
lv_2YearsAgo  TYPE NUMC4,
lv_order_by   TYPE string.
GET TIME.
lv_cYear = sy-datum+0(4).
LV_LYEAR = LV_CYEAR - 1.
LV_2YEARSAGO = LV_CYEAR - 2.
*later lv_order_by should be filled dynamically
lv_order_by = 'RRC2019 DESCENDING, RRC2018 DESCENDING'.    
SELECT      
type_name AS type,
SUM( CASE WHEN in_year eq @lv_cYear THEN record_count END ) AS RRC2019,
SUM( CASE WHEN in_year eq @lv_lyear THEN record_count END ) AS RRC2018,
SUM( CASE WHEN in_year eq @LV_2YEARSAGO THEN record_count END ) AS RRC2017
FROM entryTable
GROUP BY type_name
HAVING SUM( CASE WHEN in_year eq @lv_cYear THEN record_count END ) IN @ls_in_2019_range-select_options
AND SUM( CASE WHEN in_year eq @lv_lyear THEN record_count END ) IN @ls_in_2018_range-select_options
AND SUM( CASE WHEN in_year eq @LV_2YEARSAGO THEN record_count END ) IN @ls_in_2017_range-select_options
ORDER BY (lv_order_by)
INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

如果您尝试运行此操作，则会出现错误"语法分析器产生错误：包含IN_YEAR的表达式不是GROUP-BY表达式"。

如果将"ORDER BY(lv_ORDER_BY)"替换为"ORDER BY RRC2019 DESCENDING，RRC2018 DESCENDING"，则它正在工作。

Answer 1

我使用了@Jagger的程序，并验证了它在7.50下工作，从7.51开始中断。

这应该是一个bug。

更新：请应用注释2753729

Answer 2

我只是允许自己准备一个可编译的工作示例，它似乎与您所写的相反。我唯一更改的是INTO TABLE或(在您的情况下)INTO CORRESPONDING FIELDS OF TABLE @et_entityset的位置。

以下示例编译时没有出现问题，也不会生成任何运行时异常。

REPORT YYY.
DATA:    lv_cYear      TYPE NUMC4,
lv_lYear      TYPE NUMC4,
lv_2YearsAgo  TYPE NUMC4,
lv_order_by   TYPE string,
ls_in_2019_range TYPE RANGE OF cosp-wtg001,
ls_in_2018_range TYPE RANGE OF cosp-wtg001,
ls_in_2017_range TYPE RANGE OF cosp-wtg001.
GET TIME.
lv_cYear = sy-datum+0(4).
LV_LYEAR = LV_CYEAR - 1.
LV_2YEARSAGO = LV_CYEAR - 2.
*later lv_order_by should be filled dynamically
lv_order_by = 'RRC2019 DESCENDING, RRC2018 DESCENDING'.
SELECT
lednr AS type,
SUM( CASE WHEN GJAHR eq @lv_cYear THEN wtg001 END ) AS RRC2019,
SUM( CASE WHEN GJAHR eq @lv_lyear THEN wtg001 END ) AS RRC2018,
SUM( CASE WHEN GJAHR eq @LV_2YEARSAGO THEN wtg001 END ) AS RRC2017
FROM cosp
INTO TABLE @DATA(et_entityset)
GROUP BY lednr
HAVING SUM( CASE WHEN gjahr eq @lv_cYear THEN wtg001 END ) IN @ls_in_2019_range
AND SUM( CASE WHEN gjahr eq @lv_lyear THEN wtg001 END ) IN @ls_in_2018_range
AND SUM( CASE WHEN gjahr eq @LV_2YEARSAGO THEN wtg001 END ) IN @ls_in_2017_range
ORDER BY (lv_order_by).
BREAK-POINT.

Answer 3

下面是一个在所有最近基于ABAP的系统上编译的示例(使用SAPBC_DATA_GENERATOR程序填充SFLIGHT表)：

DATA: dummy_price TYPE sflight-price.
SELECT-OPTIONS sum_y1 FOR dummy_price DEFAULT 0 TO 99999.
SELECT-OPTIONS sum_y2 FOR dummy_price DEFAULT 0 TO 99999.
SELECT  substring( fldate, 1, 4 ) AS year,
MIN( fldate )             AS low,
MAX( fldate )             AS high
FROM sflight
GROUP BY substring( fldate, 1, 4 )
ORDER BY year
INTO TABLE @DATA(y).
ASSERT lines( y ) >= 2.
SELECT carrid,
SUM( CASE WHEN fldate BETWEEN @( y[ 1 ]-low ) AND @( y[ 1 ]-high ) THEN price END ) AS sum_year1,
SUM( CASE WHEN fldate BETWEEN @( y[ 2 ]-low ) AND @( y[ 2 ]-high ) THEN price END ) AS sum_year2
FROM sflight
GROUP BY carrid
HAVING SUM( CASE WHEN fldate BETWEEN @( y[ 1 ]-low ) AND @( y[ 1 ]-high ) THEN price END ) IN @sum_y1[]
AND SUM( CASE WHEN fldate BETWEEN @( y[ 2 ]-low ) AND @( y[ 2 ]-high ) THEN price END ) IN @sum_y2[]
ORDER BY SUM_YEAR1 DESCENDING, SUM_YEAR2 DESCENDING " <=== this works
""""ORDER BY ('SUM_YEAR1 DESCENDING, SUM_YEAR2 DESCENDING') " <=== uncomment for short dump
INTO TABLE @DATA(sflights).
LOOP AT sflights ASSIGNING FIELD-SYMBOL(<sflight>).
WRITE : / <sflight>-carrid, <sflight>-sum_year1, <sflight>-sum_year2.
ENDLOOP.

在以下两个系统上，我再现了该行为(它与静态ORDERBY子句一起使用，与动态ORDERBY语句一起使用短转储)：

• 带有SYBASE ASE的开发人员7.52 SP 1版
• S/4HANA与ABAP 7.52 SP 0

毫无疑问，某个地方存在错误，但我找不到SAP注释来更正此问题。最好在SAP支持部门开一张罚单。