The prerequisite for using the join functionality is the installation of the function module Z_THEO_READ_TABLE in SAP.
Join Functionality #
In the Joins tab you can define Table Joins.
The Join functionality of the Xtract Table component 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 to join 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: as a prerequisite for using the table join, function module Z_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 two Tables #
The following example shows, how tables KNA1 and KNVV can be joined.
- In the tab Tables and Fields, click [Add] (1) to add two tables (e.g., KNA1 and KNVV).
- Select both tables on the left and mark the fields, which you want to extract (2).
- Switch to the Joins (3) tab to define the join condition. A Join condition is automatically preset. The Join condition is based on the foreign key relationship of the joined tables,
- Optional: Switch to the WHERE clause tab and specify a WHERE clause.
Note: pay attention to the WHERE clause restrictions when specifying a WHERE-clause to the right table of table joins using a LEFT OUTER JOIN.
In the Joins tab, click Edit (pencil icon) to display joining options.
In the depicted example a left outer join on tables KNA1 (left table) and KNVV (right table) on the field KUNNR is performed. A WHERE clause on KNA1~LAND1 = ‘DE’ is specified.
Left Table, Right Table, Join Type and Join Mapping are preset with default values. They can be modified as required. The Join Mapping is based on the foreign key relationship of the joined tables.
- Click [Add] to extend the join condition to more fields.
- Click Remove (trash can icon) to remove existing joins.
- Join additional tables in the tab Tables and Fields.
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.
Auto Mapping Function (optional)
The [Auto-map] button deletes existing join conditions and performs a new field mapping based on the foreign key relationship of the joined tables.
Joining three Tables #
Example of a join with a third table KNB1: