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:
- Higher expresiveness in Open SQL ABAP SQL statements, ABAP 7.53,>Open SQL已经被更名为ABAP SQL。这个重命名反映出ABAP SQL的某些部分目前只支持特定的数据库平台(SAP HANA数据库),已经不再是全平台独立的了。游标cursor和存储过程属于native SQL;
- Code pushdown through new language features
- Reduction of existing limitations
- 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;