Avoid Long SQL Statements

SELECT_SEQ_COUNT

There are two ways in which constant SELECT_SEQ_COUNT can be useful when reading reference data:

· To improve performance (e.g. when reading 1:n groups or multiple KL objects)

· To avoid a short dump in case of excessively big Select queries.

Background: When using the 1/n function, the SQL statement may in some cases reach a length refused by the data base (e.g. when defining groups with multiple objects). Depending on the database system, the process of reading can become very slow or in exceptional cases even lead to a short dump.

By entering a numeric value in column Value from, the form of the Select can be changed to a sequential approach avoiding excessive lengths of the SQL statement. If more objects than specified in the constant are contained in one group, Allevo switches automatically to a selection in blocks. Examples:

§ Performance:
With big groups (possibly also with multiple KL objects), the reading of the reference data can slow down significantly. This phenomenon can also affect the call of SE16 if a big number of objects is entered in COSP as selection characteristic. For this reason, a block size of 50 objects is recommended (this is the default starting from version 3.2).

§ Avoiding short dump:

Assuming a group of 5000 members, representative for an order, is read without problems via the 1/n function. A 6000 order group generates the short dump. 5500 should therefore be entered in column Value from.

* Extension from version 2.9.14

Starting from this version, the constant also considers cost element groups. Allevo checks at which point the block form is most appropriate.

* Extension from version 3.1

The entry in Value from is now considered also when using the function “Pre-Select” (see the constant PRESELECT). Since selection conditions are constructed differently for this function, a separate value for the pre-select can optionally be specified in Value to.

* Extension from version 3.2

With no entry in the constant, the default value 50 is used in Allevo. If exceptional cases require reading bigger blocks, an accordingly large value should be entered in Value from.