历史版本1 :通用查询取数(适用于ECC6以上版本) 返回文档
编辑时间:
内容长度:图片数:目录数:
修改原因:
目录:
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的创建过程及出现的问题解决进行详细说明通用查询取数中的函数