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

JOIN Expression

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

"例：（NAST-OBJKY类型为CHAR30，EKKO-EBELN类型为CHAR10）
SELECT k~ebeln,
       t~kschl
  FROM nast AS t
 INNER JOIN ekko AS k ON left( t~objky, 10 ) = k~ebeln
  INTO 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~matnr
  FIELDS  mara~matnr, 
          coalesce( '截胡', “ 如果不为空则获取此变量并跳出coalesce，否则看下一个参数；
              CASE m2~coalesce WHEN  @lv_empty THEN mara~nul END, “如果为null则看下一个
              m2~coalesce, “如果为空则看下一个
              'coalesce' ) AS col2
  INTO 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 r
  LEFT OUTER JOIN spfli AS i ON i~carrid = r~carrid
 WHERE 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 NULL
   AND i~carrid IS NOT INITIAL
   AND 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;