1. 概述
1.1 版本
| FineDataLink 版本 | 功能变动 |
|---|---|
| 4.0.10 | - |
| 4.1.12.2 | 支持「选表」和「通用查询」取数 |
1.2 功能说明
通用查询比 SAP ERP 选表查询 增加了表间关联的能力，且支持多表查询。但操作起来会复杂一些，完全通过 open SQL 语句实现。
需要在 ECC6 以上的版本，并在 SAP 系统中添加一个帆软提供的 ABAP Function。
2. 添加 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.
注：若出现多个字段到一列的情况，需要将代码中的一行：CONCATENATE returnRowString '^' datafieldstring INTO returnRowString. 替换成 CONCATENATE returnRowString '|' datafieldstring INTO returnRowString.
3. 示例
在定时任务使用 SAP ERP 作为数据来源前，需要成功连接 SAP，详情请参见：配置 SAP 数据源
2.1 选择数据源
1）新建定时任务，使用「数据同步」或者「数据转换>SAP ERP输入」，如下图所示：
2）选择数据连接，并将配置方式改为「通用查询」，如下图所示：
2.2 输入查询语句
输入查询语句，如下图所示：
| 语句 | 说明 | |
|---|---|---|
| Select | T001K~MANDT,T001W~BWKEY,T001K~XEFRE | 选择 表T001K 的「MANDT」和「XEFRE」列和 表T001W 的「BWKEY」列 |
| From | T001K INNER JOIN T001W on T001K~BWKEY = T001W~BWKEY | 选择表，这里选择了 表T001K 和 表T001W，并将它们做了关联 |
| Where | T001K~MANDT <>'' AND T001K~XEFRE = '${a}' | 筛选出「MANDT」不为空，且「XEFRE」值等于参数值的数据 |
在参数列表中添加一个静态参数，如下图所示：
2.3 预览数据
点击预览，即可看到取出的数据，如下图所示：
然后即可使用其他算子进行数据处理，并将数据输出至指定的数据库中。
