This section shows how to use the integration type SAP Table or View.

Warning! Missing Authorization To use the Table component, access to the designated authority objects (RFC) in SAP must be available. For more information, refer to the knowledge base article SAP User Rights: Table.

Look Up an SAP Table or View #

  1. Create a new Service of type SAP Table or View.
  2. Click [Save and edit]. The Search SAP Table or View menu opens.
  3. Enter the name of the Table or View to be extracted in the field Search by name (1) or search for a Table description. Use wildcards ( * ) if needed. SAP-Table-or-Views
  4. Click [Search] (2) to display the search results.
  5. Select a source file from the list of available search results (3). The extraction settings of SAP Table and Views open automatically.

Settings #

The SAP Table or View settings consist of the following subsections:

yunIO-table

Table or View #

The Table or View section displays the name and description of the selected table or view.

  • Click add to add a table or view to the service.
  • Click add to remove a table or view from the service.

Advanced Settings #

Function Name

Specifies the name of the function module used for data extraction. This field is filled automatically depending on what function modules are installed on your SAP system. Custom function modules are supported.

The following function modules can be used to extract tables:

  • RFC_READ_TABLE (TAB512)
  • /BODS/RFC_READ_TABLE (TAB2048)
  • /SAPDS/RFC_READ_TABLE (TAB2048)
  • /BODS/RFC_READ_TABLE2
  • /SAPDS/RFC_READ_TABLE2
  • Z_THEO_READ_TABLE
  • THEO/READ_TABLE

Warning! Duplicates in the target environment!
The SAP standard modules for table extraction do not have pointers for table fields. In larger tables this may cause low performance and duplicates in the target environment. Use the function module THEO/READ_TABLE from Theobald Software to ensure smooth extractions.

Note the necessary authorization for SAP tables:

S_TABU_NAM ACTVT=03; TABLE=ENLFDIR

Row Limit

Specifies the maximum number of extracted records. 0 extracts the entire table.

Rows per Package

The extracted data will be split into packages of the defined size. The default value is 50000 lines.
A package size between 20000 and 50000 is advisable for large amounts of data. 0 means no packaging. Not using packaging can lead to an RFC timeout for large data extracts.

Warning! RFC_ERROR_SYSTEM_FAILURE - No more storage space available for extending an internal table:
To avoid a memory overflow on the SAP source system and to avoid huge overheads, choose a package size that suits your memory capacity.

Run in background

If this checkbox is checked, the table extraction is executed as a background job in SAP. This setting is optional and is supported in combination with function module THEO/READ_TABLE or Z_THEO_READ_TABLE version 2.0. Activate the setting Run in background for long-running extractions with a large amounts of data that may run into a timeout error (“Time limit exceeded”), when using the foreground mode.

Tip: The extraction jobs can be found in the SAP JobLog (SM37) under the JobName theo_read_table.

Output Columns #

You can select either all or only individual columns for extraction.
Per default all columns are selected to be extracted. Deselect the columns you don’t want to extract.

WHERE Clause #

A WHERE clause can be used to filter table records, see SAP ABAP Documentation: SELECT-WHERE. Enter WHERE clauses manually in Text mode or use the WHERE Clause Editor in Editor Mode.

Syntax Rules

  • Enter a space before and after the equal sign:
    Correct: YEAR = ‘1999’
    Wrong: YEAR= ‘1999 ‘, YEAR =’1999’ or YEAR=’1999’

  • Set floating point numbers in single quotation mark:
    Correct: KMENG > ‘10.3’
    Wrong: KMENG > 10.3

Note: The WHERE clause doesn’t need any line break (return key).

  • Values must have the internal SAP representation:
    • The date 01.01.1999 has the internal representation 19990101 (YYYYMMDD)
    • The year period 001.1999 has the internal representation 1999001 (YYYYPPP)
    • Numbers must contain the leading zeros, e.g., customer number 1000 has the internal representation 0000001000.
Operator Meaning
=, EQ True if the content of operand1 is equal to the content of operand2.
<>, NE True if the content of operand1 is not equal to the content of operand2.
<, LT True if the content of operand1 is less than the content of operand2.
>, GT True if the content of operand1 is greater than the content of operand2.
<=, LE True if the content of operand1 is less than or equal to the content of operand2.
>=, GE True if the content of operand1 is greater than or equal to the content of operand2.
(NOT) LIKE True if the value of operand1 matches (does not match) the pattern in operand2.
(NOT) IN True if the content of operand1 is (not) part of the content of operand2. Operand2 must be of type LIST or SQL.

Get more details on the OpenSQL syntax on the SAP help site - Select WHERE

Tip: To increase extracting performance, make sure to place the indexed fields as the first selection filter operation in the WHERE clause.

How to use the WHERE Clause in SwaggerHub

The WHERE-Clause can be parameterized in the request body of the service. Example:
WHERE-Clause in SwaggerHub

For more information on parameterizing services, refer to the following knowledge base articles:

WHERE Clause Editor #

The WHERE clause editor offers a toolkit for those who are not familiar with the syntax of the WHERE clause.
Switch to Editor mode to open the WHERE clause editor.
WHERE-Clause-Builder-01

There are 2 options for adding criteria to the WHERE clause:

  • [Add Criteria] adds single criteria.
    • The default structure for a single criteria with static values is [Column][Operator][Value] e.g., MARC~WERKS = 1000.
  • [Add Criteria Group] adds a group of criteria.
    • The default structure for a criteria group is ([Column1][Operator1][Value1][Boolean][Column2][Operator2][Value2]) e.g., (MARC~PSTAT = ‘L’ OR MARC~PSTAT = ‘LB’).

Tip: Combine multiple criteria and criteria groups to create complex filters e.g., MARC~WERKS = 1000 AND (MARC~PSTAT = ‘L’ OR MARC~PSTAT = ‘LB’) extracts only data where the column WERKS equals 1000 and the column PSTAT equals either ‘L’ or ‘LB’.

Components of the WHERE Clause Editor

WHERE-Clause-Builder-Example

The following components are available in the editor:

Icon Component Function
WHERE-Clause-Builder-Example Delete row deletes a criteria.
WHERE-Clause-Builder-Example Move row up changes the sequence of the criteria. The selected criteria moves up.
The sequence of criteria can also be changed with drag&drop.
WHERE-Clause-Builder-Example Move row down changes the sequence of the criteria. The selected criteria moves down.
The sequence of criteria can also be changed with drag&drop.
WHERE-Clause-Builder-Example Column adds a column. Click on the component to select a column from the available tables.
WHERE-Clause-Builder-Example SQL adds an Open SQL statement, see SAP Documentation: Open SQL.
WHERE-Clause-Builder-Example Operator adds an operator e.g., =, <, >, etc.
WHERE-Clause-Builder-Example Value adds a static value of type String, Number, Flag or List. List offers a separate editor to create lists of type String, Number or Select. Select enables usage of SELECT statements. For more information, refer to the knowledge base article Working with Lists in the WHERE-Clause Editor.
WHERE-Clause-Builder-Example Criteria adds a new criteria after the selected criteria.
WHERE-Clause-Builder-Example Group adds a new group of criteria the selected criteria.

Note: When adding or editing a criteria only the relevant components are displayed e.g., Add Operator is only available if there is a column or SQL statement to use an operator on.

Editing and Deleting Components

  • Click on a component to edit it. All areas that are marked green can be edited.
  • To delete a component, click the WHERE-Clause-Builder-Example icon that appears when hovering over the component.

Using SAP system fields #

Note: System fields can only be used with SAP NW 7.4 SP5 or higher and with the function modules Z_THEO_READ_TABLE or /THEO/READ_TABLE.

You can use SAP system fields for date and time.

Example:

  1. Navigate to WHERE-Clause-Editor and select a column of the type Date here: BUDAT. img-01
  2. Delete the criterion “Value” and use the criterion “SQL”. img-02
  3. Within the “SQL” criterion, use the supported system fields for date and time with a preceding “@” character, here: @sy-datum. img-03
  4. Click [Load Live Preview] to check the result.
  5. Run an extraction.

Note: The WHERE clause is overridden when a WHERE clause is entered in the designated text field or if a WHERE clause is entered in the body of the service definition when running a service.

Joins #

The Join functionality allows joining two or more tables and extract the result of the join. To perform the extraction the corresponding SQL command is generated dynamically and the join is executed on the SAP server.

Possible scenarios can be joining tables for header and item data or tables for master data and texts.

Supported join types:

  • Inner Join
  • Left Outer Join, also referred to as Left Join.

For more information on join types see SAP Online Help.

Note: To use table join, the function module /THEO/READ_TABLE needs to be available in SAP.

Note: Joining of cluster or pool tables is not supported. Cluster or pool tables need to be extracted individually and joined in the destination.

Joining Tables

The following example shows how to join the tables KNA1 and KNVV.

  1. Look up (1) the tables or views you want to join, e.g, KNA1 and KNVV.
    table-joins
  2. Define the output columns (2) you want to extract for both tables.
  3. Optional: define a WHERE clause for the tables.
  4. Click [Add join] (3). The Join Editor opens.
    join-editor
  5. If necessary, change the default values of Left Table, Right Table and Join Type.
  6. Click [Join/Add]. A new join condition is added to the join.
  7. Select a table column in Left Table field and in the Right Table field to map the table contents. It is possible to add multiple join conditions.
  8. Click [OK] to save the join.

Recommendation: to avoid poor extraction performance, do not join more than five tables.

Tip: different tables can have identical field names. Defining a join condition based on the identical field names not always delivers the expected result (e.g., VBAK~VBELN <> LIPS~VBELN). Make sure the fields you use in a join condition contain the same content/data.

SAP Customizing #

Note: SAP customizing is optional. The installation of the custom function module /THEO/READ_TABLE is recommended.

RFC_READ_TABLE Restrictions #

Especially with older SAP releases you may encounter a few restrictions when using the SAP standard function module (RFC_READ_TABLE) for table extraction:

  • The overall width of all columns to be extracted must not exceed 512 bytes.
  • Depending on the SAP version there may be other restrictions. It is not possible to extract data from tables that contain one or more columns of the data type f (FLTP, floating point), DEC (decimal, e.g. for percentage) or x (RAW, LRAW).
  • Poor extraction performance with larger tables.

When facing restrictions, install the Theobald Software custom function module /THEO/READ_TABLE on your SAP system.

Warning! Error while converting value ‘*.0’ of row 1530, column 3
The SAP standard module RFC_READ_TABLE for table extraction can only extract the ABAP data type DEC to a limited extent. This leads to the mentioned example error during extraction. Use the function module from Theobald Software /THEO/READ_TABLE.

Installation of /THEO/READ_TABLE #

An SAP transport request for the installation of the function module is provided in the installation directory: C:\Program Files\Theobald Software\yunIO\ABAP\.
Transport requests are imported into SAP by your SAP Basis team.

Note: Take a look at the README.pdf in the installation directory (e.g.,C:\Program Files\XtractProduct\ABAP\README.pdf) before installing any custom function modules.

There are two custom function modules available for Table extractions:

  • /THEO/READ_TABLE
  • Z_THEO_READ_TABLE

It is recommended to install the latest custom function module /THEO/READ_TABLE:

  • THEO_READ_TABLE_740SP05.zip - compatible with ABAP version 7.40 SP05 and higher
  • THEO_READ_TABLE_710.zip - compatible with ABAP version 7.10 and higher
  • THEO_READ_TABLE_640.zip - compatible with ABAP versions from 6.40 until 7.03
  • THEO_READ_TABLE_46C.zip - compatible with ABAP versions from 4.6C until 6.40

When importing the transport requests on older SAP releases a syntax error may occur. Contact Theobald Support and send the dedicated error message text.

Testing the module in SAP

Warning! Generating Short Dumps
The function modules /THEO/READ_TABLE and Z_THEO_READ_TABLE can only be called by Theobald products due to the callback function of the module. Testing the function modules on an SAP system is therefore not possible.

Supported Features #

Supported Features THEO_READ_TABLE_740SP05 THEO_READ_TABLE_710 THEO_READ_TABLE_640 THEO_READ_TABLE_46C
WHERE Clause x x x x
HAVING Clause x x x -
INNER JOIN x x x x
LEFT OUTER JOIN x x x x
Conversion exits x x x x
Aggregate functions x x x x
SQL expressions (subqueries) x - - -
Background jobs x x - -