Entering content frame

Background documentation Interpreting Queries Using InfoSets Locate the document in its SAP Library structure

The following explanations and examples are intended to help you understand how queries that use InfoSets work, and interpret the results correctly.

Technical Issues That Affect the Result of the Query

The results set of a join is always made up of fields from all of the tables involved. One row of this result set contains a valid combination of rows from each of the tables involved. Both the join condition and the filter for the query that you specify determine which combinations are valid.

You can set join conditions between fields from the key part of the tables and between fields from the data part of the tables. For two InfoObjects, for example, you can define an equal join condition between two attributes.

The filter for the query determines which values are allowed for individual columns of the results set, or the combinations of values that are allowed for various different columns. This further restricts the results set that is produced by the join condition.

Depending on how join conditions have been designed, every record from table1 and table2 can be included several times in a combination for a record in the results set.

For example, if for a single record in table1 there are a total of three records in table2 for which the conditions F1(T1) = F2(T2) apply, there are potentially three records in the results set in which the record from table1 is included. If table1 contains a key figure, depending on the filter condition in place, this key figure can appear one to three times or not at all in the results set. The data for the query is determined from the results set.

First of all, the data is compressed using the characteristics that you do not want to be displayed in the query. Different values for the same key figure can be output for the same combinations of characteristics in various queries, which can result in different totals.

Note

Therefore, you should note the Number of Records key figure. This is included in every InfoSet. This key figure tells you how many records in the results set for the join feed into a record in the query.

Example

You are using the following objects in a scenario:

·        DataStore object DS_ORDER

Key: ORDER_NO

Data part: PERSON, PLANT, AMOUNT, ...

·        Characteristic PLANT (time independent)

Key: PLANT

Data part (attribute): ... 

·        Characteristic PERSON (time dependent)

Data part (attribute): ...

·        Characteristic BPARTNER (time independent)

Key: BPARTNER

Data part (attribute) PLANT: ...

In the following examples it is assumed that master data exists for all of the characteristics in the data part of DS_ORDER. (Otherwise you would have to work with a left outer-join.)

...

       1.      An InfoSet contains a join from the DS_ORDER DataStore object and the characteristic PLANT. You have defined the join condition PLANT(DS_ORDER) = PLANT(PLANT). In this example, for each record in DS_ORDER there is exactly one record in PLANT. Therefore the AMOUNT key figure cannot be included multiple times in the results set.

       2.      An InfoSet contains a join from the DS_ORDER DataStore object and the characteristic BPARTNER. You have defined the join condition PLANT(DS_ORDER) = PLANT(BPARTNER ). You have to assume that several records in BPARTNER have the same value for PLANT. This means that several suitable records can be determined from BPARTNER for a single record in DS_ORDER. As a result, there is more than one record in the result set of the join and the AMOUNT key figure appears several times.

       3.      An InfoSet contains a join from the DS_ORDER DataStore object and the time-dependent characteristic PERSON. You have defined the join condition PERSON(DS_ORDER) = PERSON(PERSON). Although physically a person is unique and can exist only once, the fact that the PERSON characteristic is time dependent means that there can be several records for a single person. The use of time-dependent characteristics produces the same situation as described in the second example. Note the time selection options that are available for time-dependent characteristics in temporal joins which allow you to avoid this type of situation.

 

Leaving content frame