Join Conditions
Definition
In a relational database system, a join is a combination of data records from two or more tables. In a similar fashion, in DART, a join refers to combining data records from two or more segments in an extract.
Use
You combine data from different segments when you wish to look at data from fields that reside in those segments. In DART you define join conditions when you
create the definition of a view.For example, header data for an FI document is in the TXW_FI_HD segment, and the line item data is in the TXW_FI_POS segment. To obtain the complete data of an FI document, you need to combine (or join) the two segments in a view.
A join condition specifies the condition for combining the data from a record in the first segment with the data from a related record in the second segment. It compares the data in a field in the first segment with the data in a parallel field in the second segment.
The following diagram shows an example of a join condition, which specifies that the values in the company code (BUKRS) field in the TXW_FI_HD and the TXW_FI_POS segments should be equal:
You must specify join conditions in the definition of a DART view if the view combines data from more than one segment. You must specify at least one join condition for every pair of segments whose data you are including in the view. You may have to specify multiple join conditions for any such pair of segments.
The role of the join condition is to ensure that only relevant data records from the two segments are combined.
For example, combining data from an FI header and line item segments only makes sense, if the data records contain data of the same posting period, document number, and company code. Join conditions allow you to specify that. In the example of an FI document, you specify at least the following three join conditions:
A join condition does not necessarily have to compare values of fields with the same name. They only have to contain common data. Usually, if not using the same name, the two fields are based on the same data element or at least on the same domain. For example, the company code field in the TXW_CO_HD segment (CO header) is called REFBK. If you wish to combine the data from TXW_FI_HD and TXW_CO_HD, you join them with a condition comparing the BUKRS and REFBK fields respectively.
Default Join Conditions for DART Segments
To help you construct views, DART comes with predefined join conditions for most of the pairs of SAP-provided segments. These are the join conditions that are the most relevant when combining data records from a pair of segments.
For example, the default join conditions for combining the data of an FI document header (TXW_FI_HD) segment with an FI document line item (TXW_FI_POS) segment, check for equal document number, company code, and posting period. Having the same document number, company code, and posting period in records from these two segments ensures that the records belong to the same FI document. These three join conditions are provided as the default join conditions when you include these two segments in a DART view.
User-Defined Join Conditions for User-defined Segments
When including a user-defined segment (a segment that you or someone else has created through customer enhancements) in a view for the first time, the system displays the join condition(s) that are relevant to link it to the other segment or segments in that view. You have the option of saving these join conditions as default join conditions. To remove default join conditions, refer to
configuring data segments.Inner Join (Default)
Sometimes a record in the first segment does not have a corresponding record in the second segment with which it is joined. For example, if not every FI document item, TXW_FI_POS is related to a vendor (i.e., the vendor field can be empty). When joining the vendor master TXW_VENDOR, with an inner join, the view would only include FI document items that were vendor postings (where the vendor field was filled).
An inner join condition specifies that when combining data from two segments, only the records that have a matching value in both segments are included in the resulting view report. The view report ignores any records from the first segment where there is no matching record from the second segment. In the above example, TXW_FI_POS segment records that do not have a corresponding TXW_VENDOR segment record are not included in the view.
The default for joining segments in DART is the inner join.
Outer Join
An outer join allows all records from the combined segments to appear in the view report, regardless of whether they have a matching value in both segments.
To use the FI transaction view example above, if you choose to make the join conditions outer joins, then the view includes a TXW_FI_POS segment record, even if it does not have a matching TXW_VENDOR segment record. In the view report, if no dependent vendor is found, the vendor master fields (TXW_VENDOR) will be left blank.