如何从SAP ECC中抽取簇表数据

打开sap 客户端工具

abap 中 创建包(se80)

 

创建函数组

 

 展开abap 工作台，双击abap dictionary 字典：

 

 选择第三个data type，输入数据结构名称zsql_clause_elements，点击创建：

 

 选中structure结构，点击确定：

 

 输入简称，增加一个数据元素text，类型为so_text

 

 同样的方法，我们创建structure结构ztablerows，增加一条数据元素，名字任意如ztablerows，数据元素 char2000

 

 保存这两个structure，然后点击上方的结构树按钮，打开structure所在包目录

 

 展开structure目录，就可以看到我们定义的那两个结构了，分别右击>activate激活

 

 

 

function hhdi_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 zsql_clause_elements,

        fromclausestring type string,

        whereclauserow type zsql_clause_elements,

        whereclausestring type string,

        fieldsrow type zsql_clause_elements.

  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.

 

 

函数

l  新建abap function

注意函数的名称一定为：hhdi_extract_table_data

先把hhdi_extract_table_data的源码贴进去

 

 

点击左边的table表格tab，新增4个表，fields /fromclause /whereclause /data，类型都为like，后面的结构类型按照文档源码，前三个输入zsql_clause_elements，最后一个输入ztablerow

 

 like报参数过时的错误，回车再回车即可。

 再次check

没有错误则表示function 可以了，注意还需要设置函数可以远程访问

 

 最后再激活一下。

 

在hhdi中sap数据连接配置：

 

 

数据抽取任务的配置：

 

  

select语句参考：

select kna1~name2,knvv~kunnr,kna1~land1 from kna1 inner join knvv on kna1~kunnr = knvv~kunnr where kna1~name2 <>'' and kna1~land1 = 'us'

注意opensql不支持select * 

 

簇表数据量太大会导致sap报缓存溢出的错误，可用where语句控制其数据范围，数据量过大时，可以再hhdi的作业流程中采用循环方式取数。