|
FR连接SAP数据集帆软给出的函数有部分功能未能实现，提问了帆软的工程师说需要给SAP数据集插件做而开，想想就头痛，算了在函数里自己修改吧。修改功能点如下：
1、在做简单数据集的时候往往需要“代码”+“描述”作为下拉框给用户做勾选，原先的FR是不支持拼接写法的，突发奇想在SAP里可以这样的写法怎么到FR上就不行了：( A && '-' && B ) AS STRING，debug后发现，FR在传参的时候会在 ')'后面空格的字符串截取掉；修改了之后的代码：( A && '-' && B )^AS^STRING，并在代码处做相关的处理就可以实现拼接的写法了（包括不局限于拼接，只要是空格都可以用'^'符号代替）。注意，AS别名后的字段必须要是数据元素类型，否则查询会不生效，这是因为动态获取table时需要该字段的类型以及长度，例如：字段werks 的 数据元素是 werks_d，这时候我们需要给到的正确别名就是 werks^AS^werks_d，当然也可以用自建一个zstring数据元素，这样就不用麻烦了。
2、在做COUNT(*)^AS^INT8_LEW语法统计条数时，搭配的语句要加GROUP BY，但是SAP数据集插件是不支持GROUP BY的，所以修改了函数在Where条件框出也能进行按组统计。例： GROUP BY 字段A, 字段B......。
3、ORDER BY 排序语法与上面类似。例：ORDER BY 字段A, 字段B...... （ASCENDING/DESCENDING）。
如果各位大顾们有什么更好的解决方案在下非常愿意学习~谢谢。分享出来是看看有没有相同苦恼的，相同也想学习一下大家的方法。
TYPE-POOLS: abap.
* SELECT
DATA: columnname TYPE so_text,
rowstructdescr TYPE REF TO cl_abap_structdescr,
rowreference TYPE REF TO data,
fieldsrow TYPE zsql_clause_elements,
fielddatadescrref TYPE REF TO abap_componentdescr,
fielddescr TYPE abap_componentdescr,
fieldname TYPE string,
fielddescrtab TYPE abap_component_tab.
* FROM
DATA: fromclauserow TYPE zsql_clause_elements,
fromclausestring TYPE string.
* WHERE/ ORDER BY
DATA: whereclauserow TYPE zsql_clause_elements,
whereclausestring TYPE string.
* TABLE
DATA: returnrowstring TYPE string,
datafieldstring TYPE string,
dataline LIKE data,
numberfields TYPE i.
* 动态内表
FIELD-SYMBOLS: <datarow> TYPE any,
<datarowtable> TYPE STANDARD TABLE,
<fs_struct> TYPE any,
<datafield> TYPE any.
* 拆分字符串
DATA: ls_txt TYPE string,
lt_txt TYPE TABLE OF string,
lv_line TYPE i, "统计条数
lv_txt1 TYPE string, "拆分Where
lv_txt2 TYPE string, "拆分GROUP BY
lv_txt3 TYPE string. "拆分ORDER BY
* 转换动态参数
DATA: lr_struct TYPE REF TO data,
lr_table TYPE REF TO data.
* 处理 Select
DESCRIBE TABLE fields LINES numberfields. "计算select 条数
LOOP AT fields INTO fieldsrow.
* 分配动态参数
fieldname = sy-tabix.
CONCATENATE 'string' fieldname INTO fieldname.
CONDENSE fieldname.
* 替换'_'为' '；例：( A && B )_AS_C—> ( A && B ) AS C
DO 100 TIMES.
SEARCH fieldsrow-text FOR '^'.
IF sy-subrc EQ 0.
REPLACE '^' WITH ' ' INTO fieldsrow-text."将 ^ 符号转换为 空格
ELSE.
EXIT.
ENDIF.
ENDDO.
fielddescr-name = fieldname.
* 拆分空格，并取最后一个字符为动态参数；例 ( A && B ) AS C—> C
SPLIT fieldsrow-text AT ' ' INTO TABLE lt_txt.
lv_line = lines( lt_txt ).
READ TABLE lt_txt INTO ls_txt INDEX lv_line.
columnname = ls_txt.
REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnname WITH '-' RESPECTING CASE. "将 ~ 符号转换为 - 符号
fielddescr-type ?= cl_abap_typedescr=>describe_by_name( columnname ). "获取字段类型
APPEND fielddescr TO fielddescrtab.
* 为单一动态参数添加逗号作为查询参数，并忽略最后一条
IF sy-tabix NE numberfields.
fieldsrow-text = fieldsrow-text && ','.
ENDIF.
MODIFY fields FROM fieldsrow.
ENDLOOP.
rowstructdescr = cl_abap_structdescr=>create( fielddescrtab ).
CREATE DATA rowreference TYPE HANDLE rowstructdescr.
ASSIGN rowreference->* TO <datarow>.
* 处理 From
fromclausestring = ''.
LOOP AT fromclause INTO fromclauserow.
CONCATENATE fromclausestring fromclauserow-text INTO fromclausestring SEPARATED BY space. "换行拼接时增加空格
* CONCATENATE fromclausestring fromclauserow-text INTO fromclausestring RESPECTING BLANKS. "换行拼接时保留空格
ENDLOOP.
* 处理 Where
whereclausestring = ''.
LOOP AT whereclause INTO whereclauserow.
CONCATENATE whereclausestring whereclauserow-text INTO whereclausestring SEPARATED BY space. "换行拼接时增加空格
* CONCATENATE whereclausestring whereclauserow-text INTO whereclausestring RESPECTING BLANKS. "换行拼接时保留空格
ENDLOOP.
* 转换动态参数
DATA(lo_table) = cl_abap_tabledescr=>create( rowstructdescr ).
CREATE DATA lr_struct TYPE HANDLE rowstructdescr.
CREATE DATA lr_table TYPE HANDLE lo_table.
ASSIGN lr_struct->* TO <fs_struct>.
ASSIGN lr_table->* TO <datarowtable>.
* 处理特殊语句
SEARCH whereclausestring FOR 'GROUP BY'. "判断有无GROUP BY
IF sy-subrc EQ 0.
SPLIT whereclausestring AT 'GROUP BY' INTO TABLE lt_txt.
READ TABLE lt_txt INTO ls_txt INDEX 1. "Where 条件
lv_txt1 = ls_txt.
READ TABLE lt_txt INTO ls_txt INDEX 2. "GROUP BY 条件
lv_txt2 = ls_txt.
SEARCH whereclausestring FOR 'ORDER BY'. "判断是否同时存在ORDER BY
IF sy-subrc EQ 0.
CLEAR: lt_txt[].
SPLIT lv_txt2 AT 'ORDER BY' INTO TABLE lt_txt.
READ TABLE lt_txt INTO ls_txt INDEX 1. "Where 条件
lv_txt2 = ls_txt.
READ TABLE lt_txt INTO ls_txt INDEX 2. "ORDER BY 条件
lv_txt3 = ls_txt.
SELECT (fields) FROM (fromclausestring) INTO TABLE @<datarowtable> WHERE (lv_txt1) GROUP BY (lv_txt2) ORDER BY (lv_txt3). "GROUP BY + ORDER BY
ELSE.
SELECT (fields) FROM (fromclausestring) INTO TABLE @<datarowtable> WHERE (lv_txt1) GROUP BY (lv_txt2). "GROUP BY
ENDIF.
ELSE.
SEARCH whereclausestring FOR 'ORDER BY'. "判断有无ORDER BY
IF sy-subrc EQ 0.
SPLIT whereclausestring AT 'ORDER BY' INTO TABLE lt_txt.
READ TABLE lt_txt INTO ls_txt INDEX 1. "Where 条件
lv_txt1 = ls_txt.
READ TABLE lt_txt INTO ls_txt INDEX 2. "ORDER BY 条件
lv_txt3 = ls_txt.
SELECT (fields) FROM (fromclausestring) INTO TABLE @<datarowtable> WHERE (lv_txt1) ORDER BY (lv_txt3). "ORDER BY
ELSE.
SELECT (fields) FROM (fromclausestring) INTO TABLE @<datarowtable> WHERE (whereclausestring). "普通查询
ENDIF.
ENDIF.
* 拼接为BI接收格式
LOOP AT <datarowtable> ASSIGNING <fs_struct>.
CLEAR: returnrowstring.
DO numberfields TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE <fs_struct> TO <datafield>.
datafieldstring = <datafield>.
CONCATENATE returnrowstring '^' datafieldstring INTO returnrowstring.
ENDDO.
dataline = returnrowstring.
INSERT dataline INTO TABLE data.
ENDLOOP.
ENDFUNCTION.
| 
