PROCESSING AN EXTRACTS

PROCESSING AN EXTRACTS

Once you have declared the possible record types as field groups and defined their structure, you can fill the extract dataset using the following statements:

EXTRACT .

When the first EXTRACT statement occurs in a program, the system creates the extract dataset and adds the first extract record to it. In each subsequent EXTRACT statement, the new extract record is added to the dataset.

Each extract record contains exactly those fields that are contained in the field group , plus the fields of the field group HEADER (if one exists).

Once an extract dataset contains all of the required data, you can process it. When you have started processing it, you can no longer extract data into it.

Reading the Extract
Sorting the Extract
Control Level Processing
Calculating Numbers and Totals

Reading an Extract

Like internal tables, you can read the data in an extract dataset using a loop [Page 245].

LOOP.

...

[AT FIRST | AT [WITH ] | AT LAST.

...

ENDAT.] ... ENDLOOP.

When the LOOP statement occurs, the system stops creating the extract dataset, and starts a loop through the entries in the dataset. One record from the extract dataset is read in each loop pass. The values of the extracted fields are placed in the corresponding output fields within the loop. You can use several loops one after the other, but they cannot be nested. It is also no longer possible to use further EXTRACT statements within or after the loop. In both cases, a runtime error occurs.

In contrast to internal tables, extract datasets do not require a special work area or field symbol as an interface. Instead, you can process each record of the dataset within the loop using its original field names.

Loop control

If you want to execute some statements for certain records of the dataset only, use the control statements AT and ENDAT.

The system processes the statement blocks between the control statements for the different options of AT as follows:

·	AT FIRST
	The system executes the statement block once for the first record of the dataset.
·	AT [WITH ]
	The system processes the statement block, if the record type of the currently read extract
	record was defined using the field group . When using the WITH option, in the extract dataset, the currently read record of field group must be immediately
	followed by a record of field group .
·	AT LAST
	The system executes the statement block once for the last record of the dataset.

You can also use the AT and ENDAT statements for control level processing [Page 343].

Assume the following program is linked to the logical database [Page 1163] F1S.

REPORT DEMO.
NODES: SPFLI, SFLIGHT.
FIELD-GROUPS: HEADER, FLIGHT_INFO, FLIGHT_DATE.
INSERT: SPFLI-CARRID SPFLI-CONNID SFLIGHT-FLDATE INTO HEADER,SPFLI-CITYFROM SPFLI-CITYTO INTO FLIGHT_INFO.

START-OF-SELECTION.

GET SPFLI.
EXTRACT FLIGHT_INFO.
GET SFLIGHT.
EXTRACT FLIGHT_DATE.
END-OF-SELECTION.

LOOP.

AT FIRST.
WRITE / 'Start of LOOP'.
ULINE.
ENDAT.
AT FLIGHT_INFO WITH FLIGHT_DATE.
WRITE: / 'Info:',SPFLI-CARRID, SPFLI-CONNID, SFLIGHT-FLDATE,SPFLI-CITYFROM, SPFLI-CITYTO.

ENDAT.
AT FLIGHT_DATE.
WRITE: / 'Date:',
SPFLI-CARRID, SPFLI-CONNID, SFLIGHT-FLDATE.ENDAT. AT LAST.

ULINE.
WRITE / 'End of LOOP'.
ENDAT.
ENDLOOP.
The extract dataset is created and filled in the same way as shown in the example for Filling an Extract with Data The data retrieval ends before the END-OF-SELECTION event, in which the dataset is read once using a loop.

The control statements AT FIRST and AT LAST instruct the system to write one line and one underscore line in the list, once at the beginning of the loop and once at the end.

The control statement AT tells the system to output the fields corresponding to each of the two record types. The WITH FLIGHT_DATE option means that the system only displays the records of field group FLIGHT_INFO if at least one record of field group FLIGHT_DATE follows; that is, if the logical database passed at least one date for a flight.

The beginning of the output list looks like this: The contents of the field SFLIGHT-FLDATE in the HEADER part of record type FLIGHT_INFO are displayed as pound signs (#). This is because the logical database fills all of the fields at that hierarchy level with the value HEX 00 when it finishes processing that level. This feature is important for sorting and for processing control levels in extract datasets.

Top

Sorting an Extract

You can sort an extract dataset in much the same way as an internal table by using the following statement:

SORT [ASCENDING|DESCENDING] [AS TEXT] [STABLE]BY [ASCENDING|DESCENDING] [AS TEXT] ... [ASCENDING|DESCENDING] [AS TEXT].

The SORT statement terminates the creation of the extract dataset of a program and, at the same time, sorts its records. Without the BY option, the system sorts the dataset by the key specified in the HEADER field group.

You can sort an extract dataset as often as you like in a program, using any number of different keys. The only prerequisite is that all fields by which you want to sort are contained in the HEADER during the extraction process. You must not use the SORT statement between LOOP and ENDLOOP. However, you can sort and read the extract dataset in any sequence. No further EXTRACT statements may occur after the sort statement, otherwise a runtime error occurs.

You can define a different sort key by using the BY addition. The system then sorts the dataset according to the specified components ... . These components must either be fields of the HEADER field group or field groups containing only fields from the HEADER field group. The number of key fields is limited to 50. The sequence of the components ... determines the sort order. The system uses the options you specify before BY as a default for all fields specified behind BY. The options that you specify behind individual fields overwrite for these fields the options specified before BY.

You can define the sort direction using the DESCENDING or ASCENDING additions (ascending is the default direction). For character strings, you can use the AS TEXT addition to define the sort method. This forces an alphabetical sort, as with internal tables [Page 271]. If you want to sort an extract dataset alphabetically more than once, you should include an alphabetically-sortable field in the sort key instead of the text field for performance reasons. To fill this field, use the CONVERT statement [Page 168].

If you put AS TEXT before BY, the addition only applies to type C fields in the sort key. If you place AS TEXT after a field, the field must be of type C. If you place AS TEXT after a field group, the option only applies to the type C fields within the group.

This sorting process is not stable, that is, the old sequence of records with the same sort key must not necessarily be kept. To force a stable sort, use the STABLE addition.

If there is not enough main memory available to sort the data, the system writes data to an external auxiliary file during the sorting process. The name of the file is determined by the SAP profile parameter DIR_SORTTMP.

The SORT statement sorts by all of the fields in the sort key with the contents HEX 00 before all of the other entries. This is significant when you use logical databases [Page 1163]. When a logical database has finished reading a hierarchy level, it fills all of the fields at that level with the value HEX 00. Equally, if you use a field list in the GET [Page 958] statement (FIELDS addition), the logical database fills all of the fields not in the field list with HEX 00.

Each sorting process executed on the extract dataset using the SORT statement defines a control level. This is required for subsequent control level processing [Page 343].

REPORT DEMO.

NODES: SPFLI, SFLIGHT.

FIELD-GROUPS: HEADER, FLIGHT_INFO, FLIGHT_DATE.

INSERT: SPFLI-CARRID SPFLI-CONNID SFLIGHT-FLDATE INTO HEADER,SPFLI-CITYFROM SPFLI-CITYTO INTO FLIGHT_INFO.

START-OF-SELECTION.

GET SPFLI. EXTRACT FLIGHT_INFO.

GET SFLIGHT. EXTRACT FLIGHT_DATE.

END-OF-SELECTION.

SORT DESCENDING.

LOOP.

AT FIRST.
WRITE / 'Start of LOOP'.
ULINE.

ENDAT.
AT FLIGHT_INFO WITH FLIGHT_DATE.
WRITE: / 'Info:',SPFLI-CARRID, SPFLI-CONNID, SFLIGHT-FLDATE,SPFLI-CITYFROM, SPFLI-CITYTO.

ENDAT.
AT FLIGHT_DATE.
WRITE: / 'Date:',

SPFLI-CARRID, SPFLI-CONNID, SFLIGHT-FLDATE.ENDAT. AT LAST.

ULINE.
WRITE / 'End of LOOP'.
ENDAT.
ENDLOOP.

This example is identical with the example in the section Reading an Extract [Page 337], apart from the SORT DESCENDING statement. The SORT statement tells the system to sort the extract dataset in descending order by the three fields of the HEADER field group, before reading it using the loop. The end of the list looks like this: It is worth noting that the records with the value HEX 00 in the field SFLIGHT­FLDATE (undefined characters in the list) are sorted before the remaining records. This is done to preserve the hierarchy of the data from the logical database, independent of the sort sequence.

Top

Processing Control Levels

When you sort an extract dataset, control levels are defined in it. For general information about
control levels, refer to Processing Internal Tables in Loops [Page 299] The control level hierarchy
of an extract dataset corresponds to the sequence of the fields in the HEADER field group. After
sorting, you can use the AT statement within a loop to program statement blocks that the system
processes only at a control break, that is, when the control level changes.

AT NEW | AT END OF .
...
ENDAT.

A control break occurs when the value of the field or a superior field in the current record has
a different value from the previous record (AT NEW) or the subsequent record (AT END). Field

must be part of the HEADER field group.

If the extract dataset is not sorted, the AT... ENDAT block is never executed. Furthermore, all extract records with the value HEX 00 in the field are ignored when the control breaks are determined.

The AT... ENDAT blocks in a loop are processed in the order in which they occur. This sequence should be the same as the sort sequence. This sequence must not necessarily be the sequence of the fields in the HEADER field group, but can also be the one determined in the SORT statement.

If you have sorted an extract dataset by the fields , , ..., the processing of the control levels should be written between the other control statements as follows:

LOOP.

AT FIRST.... ENDAT.

AT NEW ....... ENDAT.
AT NEW ....... ENDAT.

...

AT ..... ENDAT.

... AT END OF .... ENDAT. AT END OF .... ENDAT. AT LAST..... ENDAT. ENDLOOP.

You do not have to use all of the statement blocks listed here, but only the ones you require.

REPORT DEMO.
DATA: T1(4), T2 TYPE I.
FIELD-GROUPS: HEADER.
INSERT T2 T1 INTO HEADER.
T1 ='AABB'. T2 = 1. EXTRACT HEADER.
T1 ='BBCC'. T2 = 2. EXTRACT HEADER. T1 ='AAAA'. T2 = 2. EXTRACT HEADER. T1 ='AABB'. T2 = 1. EXTRACT HEADER.

T1 ='BBBB'. T2 = 2. EXTRACT HEADER.
T1 ='BBCC'. T2 = 2. EXTRACT HEADER.
T1 ='AAAA'. T2 = 1. EXTRACT HEADER.
T1 ='BBBB'. T2 = 1. EXTRACT HEADER.
T1 ='AAAA'. T2 = 3. EXTRACT HEADER.
T1 ='AABB'. T2 = 1. EXTRACT HEADER.

SORT BY T1 T2.

LOOP.

AT FIRST.

WRITE 'Start of LOOP'.

ULINE.
ENDAT.
AT NEW T1.
WRITE / ' New T1:'.
ENDAT.
AT NEW T2.
WRITE / ' New T2:'.
ENDAT.
WRITE: /14 T1, T2.

AT END OF T2.
WRITE / 'End of T2'.
ENDAT.
AT END OF T1.
WRITE / 'End of T1'.
ENDAT.

AT LAST.
ULINE.
ENDAT.

ENDLOOP.

This program creates a sample extract, containing the fields of the HEADER field group only. After the sorting process, the extract dataset has several control breaks for the control levels T1 and T2, which are indicated in the following figure: In the loop, the system displays the contents of the dataset and the control breaks it encountered as follows:

Top

Calculating Numbers and Totals

When you read a sorted extract dataset using LOOP, you can access two automatically-generated fields CNT() and SUM(). These fields contain the number of different values and the sums of the numeric fields respectively. The system fills these fields at the end of a control level and after reading the last record of the dataset as follows:

· CNT()

If is a non-numeric field of the HEADER field group and the system sorted the extract dataset by , CNT() contains the number of different values assumed within the control level or entire dataset respectively.

· SUM()

If is a numeric field of the extract dataset, SUM () contains the total of the values of within the control level or entire dataset respectively.

You can access these fields either within the processing blocks following AT END OF or in the processing block following AT LAST, after reading the entire dataset. If you try to access the fields CNT() and SUM() without first sorting the dataset, a runtime error may occur.

REPORT DEMO.

DATA: T1(4), T2 TYPE I.

FIELD-GROUPS: HEADER, TEST.

INSERT T2 T1 INTO HEADER.

T1 ='AABB'. T2 = 1. EXTRACT TEST. T1 ='BBCC'. T2 = 2. EXTRACT TEST. T1 ='AAAA'. T2 = 2. EXTRACT TEST. T1 ='AABB'. T2 = 1. EXTRACT TEST. T1 ='BBBB'. T2 = 2. EXTRACT TEST. T1 ='BBCC'. T2 = 2. EXTRACT TEST. T1 ='AAAA'. T2 = 1. EXTRACT TEST. T1 ='BBBB'. T2 = 1. EXTRACT TEST. T1 ='AAAA'. T2 = 3. EXTRACT TEST. T1 ='AABB'. T2 = 1. EXTRACT TEST.

SORT BY T1 T2.

LOOP.

WRITE: /20 T1, T2.

AT END OF T2.

ULINE.

WRITE: 'Sum:', 20 SUM(T2).

ULINE.
ENDAT.

AT END OF T1.

WRITE: 'Different values:', (6) CNT(T1).ULINE. ENDAT.

AT LAST. ULINE. WRITE: 'Sum:', 20 SUM(T2),/ 'Different values:', (6) CNT(T1).ENDAT.

ENDLOOP.

This program creates a sample extract, containing the fields of the HEADER field group only. After sorting, the system outputs the contents of the dataset, the number of the different T1 fields, and the totals of the T2 fields at the end of each control level and at the end of the loop: