通用查询取数(适用于ECC 6以上版本)

编辑
文档创建者:加菲喵 (63151 )     浏览次数:5416次     编辑次数:15次     最近更新:ukae 于 2018-06-12     

目录:

1. 描述编辑

通用查询较之简单通用查询,增加了表间关联的能力,支持多表查询,但是需要在ECC6以上版本使用,且需要在SAP系统中添加一个我方提供的ABAP Function,并且通用查询相对于简单通用查询来说,设置过程没有简单通用查询那么简单,选择的表和列不再是可视化选择,而完全是通过open sql语句实现。
1.1 添加ABAP Function
我方的ABAP Function的名称为EXTRACT_TABLE_DATA,不可改变,将其添加到SAP系统中,函数内容如下:
FUNCTION EXTRACT_TABLE_DATA. *"---------------------------------------------------------------------- *"*"Local Interface: *" TABLES *" FIELDS STRUCTURE ZSQL_CLAUSE_ELEMENTS *" FROMCLAUSE STRUCTURE ZSQL_CLAUSE_ELEMENTS *" WHERECLAUSE STRUCTURE ZSQL_CLAUSE_ELEMENTS *" DATA STRUCTURE ZTABLEROWS *"---------------------------------------------------------------------- *"---------------------------------------------------------------------- *" Copy selected fields from QUERY_TABLE to DATA_STRUCTURE *"---------------------------------------------------------------------- TYPE-POOLS: abap. DATA: columnName TYPE SO_TEXT, fieldDataDescrRef TYPE REF TO abap_componentdescr, numberFields TYPE i, fieldDescr TYPE abap_componentdescr, fieldname TYPE string, fieldDescrTab TYPE abap_component_tab, rowStructDescr TYPE REF TO cl_abap_structdescr, rowReference TYPE REF TO data, returnRowString TYPE string, dataFieldString TYPE string, dataline LIKE data, fromClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE, fromClauseString TYPE string, whereClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE, whereClauseString TYPE string, fieldsRow TYPE ZALBUS_STRUCT_WHERECLAUSE. FIELD-SYMBOLS: <datarow> TYPE ANY, <datafield> TYPE ANY. * CREATE DataStructure with field names * Datatypes are read from fieldnames of FIELDS input table DESCRIBE TABLE FIELDS LINES numberFields. LOOP AT FIELDS INTO fieldsRow. fieldname = SY-TABIX. * names need to be unique and must start with a char CONCATENATE 'string' fieldname INTO fieldname. CONDENSE fieldname. fieldDescr-name = fieldname. * for dictionary lookup we need to change columnnames from Open SQL * to dictionary notation columnName = fieldsRow-TEXT. REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE. fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ). APPEND fieldDescr TO fieldDescrTab. ENDLOOP. rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ). * now we create the actual data structure in memory create data rowReference type HANDLE rowStructDescr. * finally we assign it to the Field-symbol used by the select statement ASSIGN rowReference->* TO <datarow>. * End Create DataStructure * to simplify calls we concatenate from and whereclause into strings * this way caller doesn't need to check word wrappings fromClauseString = ''. LOOP AT FROMCLAUSE INTO fromClauseRow. CONCATENATE fromClauseString fromClauseRow-TEXT INTO fromClauseString. ENDLOOP. whereClauseString = ''. LOOP AT WHERECLAUSE INTO whereClauseRow. CONCATENATE whereClauseString whereClauseRow-TEXT INTO whereClauseString. ENDLOOP. * Now start actual select operation SELECT (FIELDS) FROM (fromClauseString) INTO <datarow> WHERE (whereClauseString). * we read all fields of the current row, cast it to string and * concatenate it into a dataline with division chars. CLEAR: returnRowString. DO numberFields TIMES. ASSIGN component sy-index of structure <datarow> to <datafield>. dataFieldString = <datafield>. CONCATENATE returnRowString '|' datafieldstring INTO returnRowString. ENDDO. dataline = returnRowString. * finally dataline is added to the return table. INSERT dataline INTO TABLE data. ENDSELECT. ENDFUNCTION.
注:sap数据集支持结构型参数。

2. 示例编辑

在Function列表中选择通用查询(适用于ECC 6以上版本),如下图:
注:这里适用于6及以上版本
222
我们这里简单做个多表查询,比如说关联T001K和T001W这两个表,读取出T001K表中的MANDT和XEFRE两个列以及T001W表中的BWKEY列,并且MANDT不能为空,用参数动态输入XEFRE的值,open sql语句和sql语句的格式是有区别的,点击格式说明,可查看open sql的格式。
2.1 列设置
不同于sql语句,open sql中读取某个列应该用表~列这种格式,即select后面的文本框语句应该为:
T001K~MANDT,T001W~BWKEY,T001K~XEFRE
2.2 表设置
将两张表关联起来,open sql语句中关联表,用inner join on格式,即from后面的文本框语句应该为:
T001K INNER JOIN T001W ON T001K~BWKEY = T001W~BWKEY
2.3 条件设置
由上面的描述可知,where条件语句中的语句应该为:
T001K~MANDT <>'' AND T001K~XEFRE = '${a}'
完整的open sql语句为:
select T001K~MANDT,T001W~BWKEY,T001K~XEFRE from T001K INNER JOIN T001W ON T001K~BWKEY = T001W~BWKEY where T001K~MANDT <>'' AND T001K~XEFRE = '${a}'

最后结果如下图:
222

2.4 结果查看
点击预览按钮,可查看上面语句执行的结果,如下图:
222

3. 设置完成后效果编辑

设置完成后,点击确定,退出SAP数据集对话框,在数据集面板中会增加一个名称为SAP3的数据集,如下图:
222

FR提供了EXTRACT_TABLE_DATA 的源码及后续的数据集使用过程,EXTRACT_TABLE_DATA的创建过程及出现的问题解决进行详细说明通用查询取数中的函数

附件列表


主题: 设计思路
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

此页面有帮助吗?