Initial aggregation of selected data

We now know how we can select the correct data, which type of tables we should use with lookups and how we can ensure that we only read through relevant datasets.

In practice it is still often the case that you must select a large and/or non-defined amount of data from the database, which should then be aggregated in accordance with specific rules for the high-performance reading.

Different approaches are taken depending on the aggregation process. When adding, calculating the tally or the average, it is usually the case that you have to trawl through each dataset in the internal tables. It can also be quicker, however, when calculating the minimum or maximum value. You have to first sort the datasets contained before then cleaning with the instruction DELETE ADJACENT DUPLICATES. Using the additional COMPARING function, you also have the possibility to determine which fields should be taken into consideration when carrying out the comparison.

This is emphasised using the following example. We have a dbTab data source. This includes, among other things, 3 key fields, a lookup field for reading and a date field. It is our goal to only select the lookup value for the last, most current dataset, so that we can subsequently perform a more efficient reading.

We first select all the above mentioned fields from the dbTab, for which a key combination is available in our result_package.

SELECT

key_field1

key_field2

key_field3

date_field

lookup_field

FROM dbTab INTO TABLE iTab

FOR ALL ENTRIES IN result_package

WHERE key_field1 = result_package-key_field1

AND key_field2 = result_package-key_field2 AND key_field3 = result_package-key_field3

This means that we have several date fields for each key field, from which we only require the most current.

It is very important to sort the result table, because the DELETE ADJACENT DUPLICATES would not function otherwise.

SORT iTab BY key_field1 ASCENDING

        key_field2 ASCENDING

         key_field3 ASCENDING

         date_field DESCENDING

In this way we have ensured that the internal table iTab looks like this.

Following the instruction DELETE ADJACENT DUPLICATES, only the relevant datasets are left over and we can look forward to a high-performance reading of the datasets. :)

DELETE ADJACENT DUPLICATES FROM iTab COMPARING key_field1 key_field2 key_field3 date_field.