Child pages
  • OT-ISET-P2
Skip to end of metadata
Go to start of metadata

Left Outer: Add filter value to On-Condition

This indicator (Global Properties) is used to control how a condition on a field of a left outer table is converted in the sql statement.

This affects the query results.

  • If the indicator is set, the condition/restriction in the on-condition is added to the sql statement. If this is the case, the condition is evaluated before the join.
  • If the indicator is not set, the condition/restriction is added to the where condition. In this case, the condition is evaluated after the join

Please see also SAP note 1064698 - BW InfoSet: Include filter value in on-condition.

Example

A master data-bearing characteristic 'zprod_ho' (T00001) contains two data records.

Field (zprod_ho)

A

B

A DataStore object 'zsd_i01' (T00002) contains three data records:

Field (zprod_ho)

Date (date0)

ABC Indicator (abckey)

A

09.27.2003

X

A

04.01.2003

X

C

05.17.2003

X

The two InfoProviders are linked in the InfoSet with:

zprod_ho-zprod_ho with zsd_i01-zprod_ho

Now the following cases should be considered:

Case 1)

The objects are linked with an inner join, all fields are output in the query and a restriction is made on the date - on 04.01.2003. When all of the objects for the InfoSet are linked with an inner join, the indicator has no effect on the generated sql statement or the end result. The sequence of whether the condition is executed before the join is evaluated or after the join is evaluated is not relevant. The result is identical - no matter whether the restrictions are in the on or the where condition.

In both cases, you get the result:

Field (zprod_ho)

Field (zprod_ho)

Date (date0)

ABC indicator(abckey)

A

A

04.01.2003

X

Case 2)

The objects are linked with a left outer join (the outer condition is set on the DataStore object), all fields are output in the query and there will be a restriction on the date - on 04.01.2003.
In this case, we are assuming that the indicator is initial. In this
way, the restriction is added to the where-condition and is evaluated after the join.

For the example above, this means the following:

  • First the join is formed. This results in:

Field (zprod_ho)

Field (zprod_ho)

Date (date0)

ABC Indicator (abckey)

A

A

27.09.2003

X

A

A

01.04.2003

X

B

 

 

  • Now the restriction is applied to this result (Date = 01.04.2003). Now the result looks like this:

Field (zprod_ho)

Field (zprod_ho)

Date (date0)

ABC Indicator (abckey)

A

A

01.04.2003

X

Case 3)

The objects are linked with a left outer join (the outer condition is set on the DataStore object), all fields are output in the query and there is a restriction on the date - on 04.01.2003.
In this case, we are assuming that the indicator is not initial. In this way, the restriction is included in the on-condition and it is evaluated before the join.

For the example above, this means:

  • First the restriction is evaluated. The following record exists for the DataStore object:

Field (zprod_ho)

Date (date0)

ABC Indicator (abckey)

A

01.04.2003

X

The join is performed in the second step. Now the result looks like this:

Field (zprod_ho)

Field (zprod_ho)

Date (date0)

ABC Indicator (abckey)

Because the restriction on 04.01.2003 comes into the on condition, now you get this as the result:

A

A

04.01.2003

X

B

 

00.00.0000

 

Because the restriction on 01.04.2003 comes into the on-condition you get the result:

Field (zprod_ho)

Field (zprod_ho)

Date (date0)

ABC indicator (abckey)

A

A

01.04.2003

X

B

 

 

 

Please review SAP note 1064698 - BW InfoSet: Include filter value in on-condition where it is explained why you need to add the filter '#' for queries.

  • No labels