S4HANA For ABAP(4):OPEN SQL改变

1.19 OPEN SQL:Knowing the Syntax of New Open SQL

Open SQL更新

 

1.参数必须使用@标注，GROUP BY,ORDER BY字段之间使用“，”分隔；

2.INTO 放在最后，查询字段可以通过FIELDS关键词放在FROM后；

1.20 OPEN SQL:Using SQL Expressions

Open SQL中字段可以进行加减乘除等算术运算，Function。

示例：

"test10
"Open SQL
FORM f_test10.

  "算术运算
  "+,-,*,/
  "Number Function:ABS,CEIL,FLOOR,DIV,MOD,ROUND
  "/只能在FLTP类型之间使用
  "DIV,MOD只能处理整数类型
  SELECT
  carrid,
  connid,
  fldate,
  price * seatsmax AS amt_sum,
  seatsmax - seatsocc AS seats_remain,
  seatsocc_b + seatsocc_f AS seats_occ,
  CAST( paymentsum AS FLTP ) /
  CAST( seatsocc AS FLTP ) AS func_price,
  ABS( price ) AS func_abs,
  CEIL( price ) AS func_ceil,
  FLOOR( price ) AS func_floor,
  DIV( seatsocc,seatsmax ) AS func_div,
  MOD( seatsocc,seatsmax ) AS func_mod,
  ROUND( price,0 ) AS func_round
  FROM sflight
  WHERE carrid = 'AA'
  INTO TABLE @DATA(lt_flight).

  "字符运算
  "&&,字符串连接，可以任意数量字符串连接
  "结果最长255，只能是字符类型
  "CONCAT,字符串连接，只能两个字符串连接
  "结果最长1333,可以是字符类型字段，function,string
  "LPAD,左边字符传截取
  "LENGTH,字符长度
  "LTRIM,左边字符删除
  "REPLACE,字符串替换
  "RIGHT,右边字符串截取
  "RTRIM,右边字符删除
  "SUBSTRING,字符串截取
  SELECT
  carrid,
  connid,
  fldate,
  'Plane Type:' && planetype && '-END' AS plane_type,
  CONCAT( 'Plane Type:',planetype ) AS plane_type1,
  LPAD( 'HELLO',4,'H' ) AS func_lpad,"HELL
  LENGTH( planetype ) AS func_length,
  LTRIM( 'HELLO','H' ) AS func_ltrim, "ELLO
  REPLACE( 'HELLO','L','@' ) AS func_replace, "HE@@O
  RIGHT( 'HELLO',3 ) AS func_right, "LLO
  RTRIM( 'HELLO','O' ) AS func_rtrim, "HELL
  SUBSTRING( 'HELLO',1,3 ) AS func_substring "HEL
  FROM sflight
  WHERE carrid = 'AA'
  INTO TABLE @DATA(lt_flight1).
  "显示结果
  LOOP AT lt_flight1 INTO DATA(ls_flight1).
    WRITE:/ ls_flight1-carrid,ls_flight1-connid,ls_flight1-fldate,
            ls_flight1-plane_type,ls_flight1-plane_type1,
            'LPAD:',ls_flight1-func_lpad,
            'LENGTH:',ls_flight1-func_length,
            'LTRIM:',ls_flight1-func_ltrim,
            'REPLACE:',ls_flight1-func_replace,
            'RIGHT:',ls_flight1-func_right,
            'RTRIM:',ls_flight1-func_rtrim,
            'SUBSTRING:',ls_flight1-func_substring.
  ENDLOOP.

  "CASE WHEN
  SELECT
  carrid,
  connid,
  fldate,
  CASE WHEN seatsmax - seatsocc > 0 THEN 'Seats Free'
       WHEN seatsmax - seatsocc < 0 THEN 'Seats Error'
  ELSE 'Seats Full'
  END AS case_status
  FROM sflight
  WHERE carrid = 'AA'
  INTO TABLE @DATA(lt_flight2).


  "FOR ALL ENTRY IN替换写法
  SELECT * FROM spfli
  WHERE carrid IN ('AA','DL')
    AND cityfrom = 'NEW YORK'
  INTO TABLE @DATA(lt_spfli).
  SELECT carrid,connid,fldate FROM sflight
  FOR ALL ENTRIES IN @lt_spfli
  WHERE carrid = @lt_spfli-carrid
    AND connid = @lt_spfli-connid
  INTO TABLE @DATA(lt_sflight3).
  "更新后写法
  WITH +connect AS ( SELECT FROM spfli AS a
                         FIELDS a~carrid,a~connid
                         WHERE carrid IN ('AA','DL')
                         AND cityfrom = 'NEW YORK' )
  SELECT FROM +connect INNER JOIN sflight AS b
  ON +connect~carrid = b~carrid
  AND +connect~connid = b~connid
  FIELDS b~carrid,b~connid,b~fldate
  INTO TABLE @DATA(lt_sflight4).
  LOOP AT lt_sflight4 INTO DATA(ls_sflight4).
    WRITE:/ ls_sflight4-carrid,ls_sflight4-connid,ls_sflight4-fldate.
  ENDLOOP.


  "EXISTS替换写法
  SELECT * FROM spfli AS a
  WHERE EXISTS
  ( SELECT * FROM sflight
    WHERE carrid = a~carrid
    AND connid = a~connid
    AND carrid = 'AA' )
  INTO TABLE @DATA(lt_spfli1).
  LOOP AT lt_spfli1 INTO DATA(ls_spfli1).
    WRITE:/ ls_spfli1-carrid,ls_spfli1-connid,ls_spfli1-cityfrom.
  ENDLOOP.
  "更新后写法
  WITH +connect AS ( SELECT FROM sflight AS a
                         FIELDS a~carrid,a~connid
                         WHERE a~carrid = 'AA' )
  SELECT FROM +connect INNER JOIN spfli AS b
  ON +connect~carrid = b~carrid
  AND +connect~connid = b~connid
  FIELDS b~carrid,b~connid,b~cityfrom
  INTO TABLE @DATA(lt_spfli2).
  LOOP AT lt_spfli2 INTO DATA(ls_spfli2).
    WRITE:/ ls_spfli2-carrid,ls_spfli2-connid,ls_spfli2-cityfrom.
  ENDLOOP.

ENDFORM.

1.21 OPEN SQL: Knowing the New Join Possibilities

 

1.Open SQL支持多种JOIN类型；

示例：

"SELECT APPENDING
"当需要从多个table查询数据到同一个内表时
"SELECT * INTO lt_1 FROM table1.
"SELECT * APPENDING lt_1 FROM table2.
"更新后写法，使用UNION
SELECT * FROM table1
UNION
SELECT * FROM table2
INTO TABLE @DATA(lt_2).