Joining Source Tables for Value Determination

In the Customizing settings for ILM rules, source fields of the ILM objects for the value determination of condition fields, time references, and time offsets are specified. At runtime, the system derives the relevant values for rule evaluation from these source fields.

If the source fields come from more than one source table, the system uses a join operation to link their values from the relevant source tables at runtime. The system evaluates the ILM rules of the relevant ILM object for each record from the result of this relationship.

In the Join Definitions dialog of the Object Category-Specific Settings for ILM objects of the SAP Business Suite object category, conditions have been defined for the relationships of those cases in which the data from two source tables is joined not by the Cartesian product but instead by a full outer join while taking additional equality conditions for the source tables into account. This is especially true if the ILM object uses multiple item tables, which may contain multiple data records, as the source table for rule determination. If this is the case, the classic Cartesian product does not produce a combination of field values that makes sense. See Table 9 below.

Cartesian Product without Join Definitions

If the Cartesian product of the data record is to be created from two source tables, you do not need to make any entries in the Join Definitions dialog.

Example Example

Table 1: SBOOK Table
Selection Field
CARRID	CONNID
AA	0400
LH	0400

Table 2: SFLIGHT Table
Selection Field
CARRID	CONNID
AA	0401
LH	0400

Without additional conditions (Cartesian product), you obtain the following result when SBOOK and SFLIGHT are joined.

Table 3: Result of the Join
SBOOK		SFLIGHT
CARRID	CONNID	CARRID	CONNID
AA	0400	AA	0401
AA	0400	LH	0400
LH	0400	AA	0401
LH	0400	LH	0400

End of the example.

Joining with Additional Equality Conditions

Combinations are created without additional conditions in the above example in which SBOOK-CARRID is not equal to SFLIGHT-CARRID and SBOOK-CONNID is not equal to SFLIGHT-CONNID. To prevent illogical combinations of this type, you can define additional equality conditions for joining data from different tables under Join Definitions.

Note Note

If join definitions are defined, the system always performs a Full Outer Join to join the data from the relevant tables so that all data records from the source tables are contained in the relationship (taking into account the equality conditions defined in the Join Definitions).

End of the note.

Example Example

The following conditions are also valid for the joined data:

SBOOK-CARRID = SFLIGHT-CARRID
SBOOK-CONNID = SFLIGHT-CONNID

You achieve this by making the following entries in Join Definitions.

Table 4: Join Definition 1
Join No.	Table Left	Field Right	Table Right	Field Right
1	SFLIGHT	CARRID	SBOOK	CARRID
2	SFLIGHT	CONNID	SBOOK	CONNID

The result of the Join link for SBOOK and SFLIGHT that takes the link condition into account looks like this:

Table 5: Result of the Join
SBOOK		SFLIGHT
CARRID	CONNID	CARRID	CONNID
AA	0400	Initial	Initial
LH	0400	LH	0400
Initial	Initial	AA	0401

End of the example.

Linking Three Tables

The following example displays how the three tables are joined together:

Example Example

Table 6: Table SPFLI
Selection Fields
CARRID	CONNID
AC	0700
LH	0400

Table 7: Join Definition
Join No.	Table Left	Field Left	Table Right	Field Right
1	SFLIGHT	CARRID	SBOOK	CARRID
1	SFLIGHT	CONNID	SBOOK	CONNID
2	SPFLI	CARRID	SFLIGHT	CARRID
2	SPFLI	CONNID	SFLIGHT	CONNID

Table 8: Result of the Join
SBOOK		SFLIGHT		SPFLI
CARRID	CONNID	CARRID	CONNID	CARRID	CONNID
AA	0400	Initial	Initial	Initial	Initial
LH	0400	LH	0400	LH	0400
Initial	Initial	AA	0401	Initial	Initial
Initial	Initial	Initial	Initial	AC	0700

End of the example.

Avoiding Illogical Links

You need to formulate the join definitions so that illogical combinations of field values are not possible.

Example Example

The following example describes a badly selected Join: The data object contains the above data records for the ILM objects SBOOK and SPFLI. However, SFLIGHT does not contain any data. The additional conditions are as in Join Definition 2. The result looks like this:

Table 9: Result of the Join
SBOOK		SPFLI
CARRID	CONNID	CARRID	CONNID
AA	0400	AC	0700
AA	0400	LH	0400
LH	0400	AC	0700
LH	0400	LH	0400

Although the join conditions are fulfilled, the result may not be what you wanted as it contains records that do not match CARRID and CONNID.

End of the example.