Aggregate Functions usage in ABAP Program

n You can use aggregate functions (COUNT, SUM, MAX, MIN, AVG) to perform calculations using the DBMS. Only use aggregate functions if they significantly reduce the quantity of data records to be transferred.

n Aggregate functions are usually used used with a GROUP BY clause. SQL statements that contain aggregate functions bypass R/3 table buffers. (Note that this can be a disadvantage if tables are buffered.)

n Field values are processed differently, depending on whether they are in ABAP or at database level. The database uses a different method than ABAP for rounding data (for example, when converting F to I). The database recognizes the value NULL. ABAP does not recognize the value NULL.

For the aggregate function AVG, use data type F (number with floating point) for the target field. For the aggregate function SUM, make the data type of the target field longer than the data type of the source field, to allow for overflow.

Using Having Clause in a sql query:

n To apply a logical condition to the groups defined in the GROUP BY clause, use HAVING with a SELECT statement. The database determines the resulting quantity, that is, the data quantity to be transferred depends on the selectivity of the HAVING clause.

n Using HAVING clauses is similar to using aggregate functions. HAVING clauses create an extra load on the database. Therefore, only use HAVING clauses if they significantly reduce the quantity of data records to be transferred. Before you use a HAVING clause, check whether the condition can be specified in the SELECT clause instead.

Using Array Fetch:

n For SELECT .. ENDSELECT and ARRAY FETCH, the same number of data records must be transferred from the database server to the application server. Since data records are transferred in 32 KB blocks, the transfer load (that is, the number of fetches) is the same for both variants.

n However, an ARRAY FETCH is preferable to a SELECT ... ENDSELECT loop, because data records are transferred per record from the database interface to the ABAP program in a SELECT .. ENDSELECT. Whereas, in an ARRAY FETCH, the data records are transferred to the ABAP program in a block.

Using up to n rows in sql query:

n There are two ways to read a fixed number of data records. You can transfer the data records to the application server using SELECT … ENDSELECT and then determine if the required number of rows has been read. However, using SELECT … UP TO n ROWS is preferable, because only the requested number of data records are transferred from the database to the application.

n In the second version, the UP TO n ROWS addition ensures that only the required data quantity is read from the database and transferred to the database interface.

68

RELATED POST

ACCESSING INDIVIDUAL TABLES IN SAP ABAP

DATA PART TWO SAP ABAP

DATA PART THREESAP ABAP

DATA PART FOURSAP ABAP

DATA PART FIVESAP ABAP

DATA PART SIXSAP ABAP