点击此处---> 群内免费提供SAP练习系统(在群公告中)
加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)
SAP 增强了 Open SQL 并添加了许多新功能。
其中一项更改是对主机/程序变量(变量/结构/表)使用转义字符“@”。早些时候,我们可以在 SQL 查询中使用程序变量,就好像 SQL 是 ABAP 本身的一部分一样。在 ABAP 7.40 中,'@' 被引入以明确提及 @ 后面的任何内容都是程序变量。这使得 Open SQL 解析器可以清楚地区分由数据库计算的操作数和内容必须传递到数据库的 ABAP 变量。
因此,让我们看看应该如何编写简单的查询以及如何让事情变得真正有趣。这篇文章中提到的语法是指 7.52 版本,在早期版本中可能有一些东西可能不起作用。
Host Variable (@), comma separated list and inline declaration
选择字段列表现在用逗号分隔,所有变量都以 @ 开头,并且表被声明为内联。
"Before 7.40 TYPES: BEGIN OF flight_type, carrid TYPE sbook-carrid, connid TYPE sbook-connid, fldate TYPE sbook-fldate, END OF flight_type. DATA result TYPE STANDARD TABLE OF flight_type. SELECT carrid connid fldate FROM sflight INTO TABLE result WHERE carrid IN s_carrid. "From 7.40 SELECT carrid, connid, fldate FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
INTO 子句移到语句的末尾,因为这是 INTO 子句的新位置。大多数查询仍然有效,但一些包含算术运算的查询会要求您将子句移动到结尾。
(如果您不熟悉内联声明 - 请查看 ABAP 7.4 及更高版本的帖子 [1]:内联数据声明。
Change column names
列名也可以使用别名。下面的查询将创建带有字段carrier、connection 和flight_date 的表flights_tab。
SELECT carrid AS carrier, connid AS connection, fldate AS flight_date FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
Literals as additional columns
SELECT carrid, connid, fldate, 'Extra Column' AS new_column FROM sflight WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
Arithmetic Operations
SELECT carrid, connid, fltime + 120 AS total_time DIVISION( fltime, 60, 2 ) AS fltime FROM spfli WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
String Operations
SELECT SINGLE CONCAT( char1, char2 ) AS concat, LENGTH( char1 ) AS length, LEFT( char1, 3 ) AS left, LOWER( char2 ) AS lower, UPPER( char2 ) AS upper, RIGHT( char1, 3 ) AS right, SUBSTRING( char1, 3, 3 ) AS substring, REPLACE( char1, ',' , '.' ) AS replace, CONCAT_WITH_SPACE( char1, char2, 1 ) AS concat_with_space FROM demo_expressions INTO @DATA(result).
Date Functions
SELECT SINGLE dats_is_valid( dats1 ) AS valid, dats_days_between( dats1, dats2 ) AS days_between, dats_add_days( dats1, 100 ) AS add_days, dats_add_months( dats1, -1 ) AS add_month FROM demo_expressions INTO @DATA(result).
Case Statements – Simple case
"Simple Case SELECT num1, CASE num1 WHEN num1 < 50 THEN 'Less than 50' WHEN num1 > 50 THEN 'More than 50' ELSE 'Equal to 50' END AS group FROM demo_expressions INTO @DATA(result)
Case Statements – Complex Case
"Complex Case / Searched case SELECT num1, num2, CASE WHEN num1 < 50 AND num2 < 50 THEN 'Both less than 50' WHEN num1 >= 50 AND num2 >= 50 THEN 'Both more than 50' ELSE 'Others' END AS group FROM demo_expressions INTO @DATA(result).
聚合查询
HANA 的重要转变是聚合查询的使用。使用 HANA 的代码下推方法,聚合查询从不推荐变为推荐。
一个简单的聚合查询如下。
SELECT carrid, SUM( price ) AS total_price, FROM sflight GROUP BY carrid INTO TABLE @DATA(result).
除此之外,还可以使用我们作为字段列表的一部分在 GROUP BY... HAVING 子句中使用的表达式。
SELECT num1 + num2 AS sum, COUNT( * ) AS count FROM demo_expressions GROUP BY num1 + num2 HAVING COUNT( * ) > 10 ORDER BY sum INTO TABLE @DATA(result).
Client Handling
SELECT * FROM sflight USING CLIENT '100' WHERE carrid IN @s_carrid INTO TABLE @DATA(result).
总而言之,在选择查询中我们可以做很多事情,我们应该尝试有效地使用查询来减少循环和合并数据的开销。
在 SQL 增强的第 2 部分中,我们将探讨 Joins 和 Select from Internal Table 的变化。是的,你没看错。