ABAP--新语法--Open SQL--第五天-- JOIN Expression / Common Table Expressions (CTE) / WHERE Condition

JOIN Expression

 在特定的应用场景中，需要使用字符长度不一致的两个字段进行关联时，可以使用相应的表达式处理，但要注意表达式的位置，一般需要放在等式左边，如下例

"例：（NAST-OBJKY类型为CHAR30，EKKO-EBELN类型为CHAR10）
SELECT k~ebeln,t~kschlFROM nast AS tINNER JOIN ekko AS k ON left( t~objky, 10 ) = k~ebelnINTO TABLE @DATA(lt_data).

动态SQL，注意select语句括号没空格；

DATA : lv_select TYPE string ,lv_dbtab TYPE string ,BEGIN OF lv_data8,cnam like scarr-carrname,int8 type int8,END OF lv_data8,lt_data8 like TABLE OF lv_data8.lv_select = `c~carrname as cnam, int8``99`` as int8` .lv_dbtab = `( scarr AS c `& ` INNER JOIN spfli AS p ON p~carrid  = c~carrid )`.
SELECT(lv_select)FROM  (lv_dbtab)INTO TABLE @lt_data8 "@data(lt_data8)UP TO 5 rows.
CL_DEMO_OUTPUT=>DISPLAY( LT_DATA8 ).

Common Table Expressions (CTE)

SQL Enhancements：

1. Higher expresiveness in Open SQL ABAP SQL statements, ABAP 7.53，>Open SQL已经被更名为ABAP SQL。这个重命名反映出ABAP SQL的某些部分目前只支持特定的数据库平台（SAP HANA数据库），已经不再是全平台独立的了。游标cursor和存储过程属于native SQL；
2. Code pushdown through new language features
3. Reduction of existing limitations
4. Flexible consumption of CDS modeling entities

在from子句使用子查询；

" CTE, coalensce, max join 50, max sub_query 50;DATA: lv_empty(8) TYPE c.WITH  +mara( matnr, ersda, nul ) AS ( SELECT matnr, ersda, lvorm  FROM mara  ), "UP TO 3 ROWS+m2( matnr, coalesce ) AS ( SELECT matnr, @lv_empty AS coalesce FROM +mara )SELECT FROM +mara AS mara LEFT OUTER JOIN  +m2 AS m2 ON mara~matnr = m2~matnrFIELDS  mara~matnr, coalesce( '截胡', “ 如果不为空则获取此变量并跳出coalesce，否则看下一个参数；CASE m2~coalesce WHEN  @lv_empty THEN mara~nul END, “如果为null则看下一个m2~coalesce, “如果为空则看下一个'coalesce' ) AS col2INTO TABLE @DATA(lt_data6)UP TO 8 ROWS.cl_demo_output=>display( lt_data6 ).

WHERE Condition

 常用的条件语句，整理如下：
[ NOT ] IN：除了 SELECT-OPTION，也支持多个自定义或者通过子查询获取的值
ANY/SOME/ALL：允许将子查询获取到的结果集作为限制条件使用
[ NOT ] BETWEEN … AND …：使用范围条件
[ NOT ] LIKE … [ ESCAPE ]：使用模糊查询
IS [ NOT ] NULL：判断是否有关联到相应的记录
IS [ NOT ] INITIAL：判断是否为空值
[ NOT ] EXISTS：根据指定条件到数据库表查询数据，判断查询结果是否存在
like_regexpr：匹配正则表达式

SELECT i~*FROM scarr AS rLEFT OUTER JOIN spfli AS i ON i~carrid = r~carridWHERE i~carrid IN ( 'AA' , 'CO' )AND i~carrid IN ( SELECT DISTINCT carrid FROM sflight ) AND i~carrid = ANY ( SELECT DISTINCT carrid FROM sflight )AND i~carrid NOT BETWEEN 'BA' AND 'CA'AND ( i~carrid LIKE 'A%' OR i~carrid LIKE 'C%' )AND r~carrid IS NOT NULLAND i~carrid IS NOT INITIALAND EXISTS ( SELECT carrid FROM sflight WHERE carrid = i~carrid )INTO TABLE @DATA(lt_data). 

where like_regexpr( pcre = '^[Aa]\s*\w+', value = mara~matnr ) > 0
更多精彩尽在se26: CL_ABAP_MATCHER, CL_ABAP_REGEX;