通用查询取数(适用于ECC 6以上版本)
目录:
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列表中选择，如下图：
我们这里简单做个多表查询，比如说关联KNA1和KNVV这两个表，读取出KNA1表中的NAME2和LAND1两个列以及KNVV表中的KUNNR列，并且NAME2不能为空，用参数动态输入LAND1的值，open sql语句和sql语句的格式是有区别的，点击，可查看open sql的格式。
2.1 列设置
不同于sql语句，open sql中读取某个列应该用表~列这种格式，即select后面的文本框语句应该为：
KNA1~NAME2,KNVV~KUNNR,KNA1~LAND1
2.2 表设置
将两张表关联起来，open sql语句中关联表，用inner join on格式，即from后面的文本框语句应该为：
KNA1 INNER JOIN KNVV ON KNA1~KUNNR = KNVV~KUNNR
2.3 条件设置
由上面的描述可知，where条件语句中的语句应该为：
KNA1~NAME2 <>'' AND KNA1~LAND1 = '${land}'
完整的open sql语句为：
select KNA1~NAME2,KNVV~KUNNR,KNA1~LAND1 from KNA1 INNER JOIN KNVV ON KNA1~KUNNR = KNVV~KUNNR where KNA1~NAME2 <>'' AND KNA1~LAND1 = '${land}'
最后结果如下图：
2.4 结果查看
点击预览按钮，可查看上面语句执行的结果，如下图：
3. 设置完成后效果
设置完成后，点击确定，退出SAP数据集对话框，在数据集面板中会增加一个名称为的数据集，如下图：
FR提供了EXTRACT_TABLE_DATA 的源码及后续的数据集使用过程，EXTRACT_TABLE_DATA的创建过程及出现的问题解决进行详细说明通用查询取数中的函数
附件列表
主题： 设计思路
标签：
暂无标签