Examples of Condition Conversion

Use

The following examples show how the Left Outer: Include Filter Value in On-Condition indicator affects the query results.

Characteristic ZPRODUCT (T00001), which has master data, contains two data records.

#Field ( ZPRODUCT )

DataStore object ZSD_01 (T00002) contains three data records:

#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	27.09.2003	X
A	01.04.2003	X
C	17.05.2003	X

In the InfoSet, the two InfoProviders are joined: ZPRODUCT- ZPRODUCT with ZSD_01- ZPRODUCT

Note the following cases:

Case 1

The objects are joined using an inner join. All the fields are output in the query and a restriction is applied to the date (01.04.2003). If all the objects in the InfoSet are joined using an inner join, this indicator does not effect the SQL statement that is generated, or the end result. It does not matter whether the condition is executed before the join is evaluated or afterwards. The result is the same, whether the restrictions apply in the on-condition or the where-condition.

In both cases, the result is:

#Field (ZPRODUCT)	#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	A	01.04.2003	X

Case 2

The objects are joined using a left outer join (the outer condition is set for the DataStore object). All the fields are output in the query and a restriction is applied to the date (01.04.2003).

In this case, we assume that the indicator is initial. The restriction is included in the where-condition and is evaluated after the join.

This means:

First, the join is built. The results are as follows:

#Field (ZPRODUCT)	#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	A	27.09.2003	X
A	A	01.04.2003	X
B

The restriction is applied to these results (date = 01.04.2003).

The results are as follows:

#Field (ZPRODUCT)	#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	A	01.04.2003	X

Case 3

The objects are joined using a left outer join (the outer condition is set for the DataStore object). All the fields are output in the query and a restriction is applied to the date (01.04.2003).

In this case, we assume that the indicator is not initial. The restriction is included in the on-condition and is evaluated before the join.

This means:

First, the restriction is applied. The following record is produced for the DataStore object:

#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	01.04.2003	X

In the second step, the join is performed. The results are as follows:

#Field (ZPRODUCT)	#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	A	01.04.2003	X
B

The restriction is applied in the on-condition. The result is as follows:

#Field (ZPRODUCT)	#Field (ZPRODUCT)	#Date (0DATE)	ABC Indicator (ABCKEY)
A	A	01.04.2003	X
B