网站新版,网站后台分模块,深圳产品外观设计公司,网络游戏排行榜前十手游SAP ABAP 语法更新之Open SQL1、前言2、演示1、前言
自从 SAP 推出 SAP ON HANA#xff0c;与之相随的 AS ABAP NW 7.40 版本以后#xff0c;ABAP 语法也有了较多的更新#xff0c;本篇对 Open Sql的语法更新部分做一个DEMO演示。
NW 7.40 以前 OpenSQL 的限制#xff1a…
SAP ABAP 语法更新之Open SQL1、前言2、演示1、前言
自从 SAP 推出 SAP ON HANA与之相随的 AS ABAP NW 7.40 版本以后ABAP 语法也有了较多的更新本篇对 Open Sql的语法更新部分做一个DEMO演示。
NW 7.40 以前 OpenSQL 的限制 ① 没有固定值或计算列 ② 没有Case表达式 ③ 没有 right outer join ④ 没有 Union ⑤ 没有子查询
NW 7.40 开始 OpenSQL 支持的新特性 ① CASE ② COALESCE ③ 算术表达式-*/ ④ CAST类型转换 ⑤ 算术函数DIV、MOD、ABS、FLOOR、CEIL、ROUND ⑥ 聚合函数SUMCOUNTAVGMAXMIN ⑦ 字符串连接符 ⑧ right outer join ⑨ union and union all 存在的限制 ① 无法使用 SAP HANA 内置函数如DAYS_BETWEEN ② 无法使用 SAP HANA 数据库视图和存储过程
2、演示
*---------------------------------------------------------------------*
* Report Y_XLEVON_OPEN_SQL
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
REPORT y_xlevon_open_sql.TYPES: BEGIN OF ty_sflight,carrid TYPE sflight-carrid,connid TYPE sflight-connid,fldate TYPE sflight-fldate,seatsmax TYPE sflight-seatsmax,seatsocc TYPE sflight-seatsocc,status TYPE char10,END OF ty_sflight.PARAMETERS:p_carrid TYPE sflight-carrid,p_cust TYPE scustom-name.* 1CASE表达式
* Old syntax for before AS ABAP 7.4 SP05
DATA: gt_data TYPE STANDARD TABLE OF ty_sflight.
FIELD-SYMBOLS: fs_sflight TYPE ty_sflight.SELECTcarridconnidfldateseatsmaxseatsocc
FROM sflight
INTO TABLE gt_data
WHERE carrid p_carrid.LOOP AT gt_data ASSIGNING fs_sflight.IF fs_sflight-seatsocc GT 300.fs_sflight-status High OCC.ELSE.fs_sflight-status Low OCC.ENDIF.
ENDLOOP.* AS ABAP 7.4 SP05
SELECTcarrid,connid,fldate,seatsmax,seatsocc,CASE WHEN seatsocc GT 300 THEN High OCCELSE Low OCCEND AS status
*into table DATA(gt_data2) syntax OK
FROM sflight
*into table DATA(gt_data2) syntax OK
WHERE carrid p_carrid
INTO TABLE DATA(gt_data2).* New Syntax 7.5
SELECT FROM sflightFIELDS carrid,connid,fldate,seatsmax,seatsocc,CASE WHEN seatsocc GT 300 THEN High OCCELSE Low OCCEND AS status
*into table DATA(gt_data3). syntax error
WHERE carrid p_carrid
INTO TABLE DATA(gt_data3). 必须放在最后* 2字面算术表达式
DATA: lv_rebate TYPE p DECIMALS 2 VALUE 0.10.
SELECThello XLevon AS char, 字符串拼接100 AS i_num, 整型sy-langu AS sys_lan, 系统语言lv_rebate AS p_num, 定点小鼠price,( price * lv_rebate ) AS rebate, 折扣round( ( price * lv_rebate ), 1 ) AS round_col, 折扣保留一位小数ceil( ( price * lv_rebate ) ) AS ceil_col,floor( ( price * lv_rebate ) ) AS floor_col
FROM sflight
WHERE carrid p_carrid
INTO TABLE DATA(gt_data_expression).* 3聚合函数
SELECTcarrid,connid,COUNT( DISTINCT ( fldate ) ) AS distinct_fldate,SUM( seatsmax ) AS sum_seatsmax,CAST( AVG( seatsocc ) AS INT4 ) AS avg_seatsocc
FROM sflight
WHERE carrid p_carrid
GROUP BY carrid, connid
INTO TABLE DATA(gt_data_aggregate).* 4右外连接
SELECTscarr~carrid,scarr~carrname,spfli~connid,spfli~cityfrom,spfli~cityto
FROM scarr
RIGHT OUTER JOIN spfli ON spfli~carrid scarr~carrid
WHERE coalesce( scarr~carrid, NO CARR ) NO CARR
ORDER BY scarr~carrid, spfli~connid
INTO TABLE DATA(gt_data_COALESCE)UP TO 10 ROWS.* 5UNION sub query
SELECT FROM sbookFIELDS 1 AS type, carrid, connid, fldate, bookid,CAST( loccuram AS FLTP ) / CAST( 2 AS FLTP ) AS div_field,loccurkey
WHERE customid IN ( SELECT id FROM scustom WHERE name Martin )
UNION ALL
SELECT FROM sbookFIELDS 2 AS type, carrid, connid, fldate, bookid,CAST( loccuram AS FLTP ) / CAST( 2 AS FLTP ) AS div_field,loccurkey
WHERE customid IN ( SELECT id FROM scustom WHERE name p_cust )
ORDER BY type, carrid, connid, fldate, bookid
INTO TABLE DATA(gt_data_union).* 6应用判断数据记录是否存在
DATA: lv_msg TYPE string.
SELECT SINGLE abap_trueFROM scustomINTO DATA(lv_exists)WHERE name p_cust.
IF lv_exists EQ abap_true.CONCATENATE Customer p_cust Exists INTO lv_msg SEPARATED BY space.
* lv_msg Customer p_cust Exists. 前两个单词间空格丢失
ELSE.lv_msg Customer | { p_cust } doesnt Exists|.
ENDIF.cl_demo_outputwrite( gt_data ).
cl_demo_outputwrite( gt_data2 ).
cl_demo_outputwrite( gt_data3 ).
cl_demo_outputwrite( gt_data_expression ).
cl_demo_outputwrite( gt_data_aggregate ).
cl_demo_outputwrite( gt_data_COALESCE ).
cl_demo_outputwrite( gt_data_union ).
cl_demo_outputwrite( lv_msg ).
cl_demo_outputdisplay( ).原创文章转载请注明来源-X档案
