SORT - Sorting an Extract Dataset

Basic form

SORT.

Extras:


1. ... BY f1 f2 ... fn

2. ... ASCENDING

3. ... DESCENDING

4. ... AS TEXT

5. ... STABLE

Effect

Sorts the dataset generated by the EXTRACT statement with the sort key derived from the fields in the field group HEADER in ascending order (see also FIELD-GROUPS).


Addition 1

... BY f1 f2 ... fn

Effect

Uses the sort key defined by f1, f2, ..., fn instead of the sort key derived from the fields in the field group HEADER. Here, each fi field must be either a field of the field group HEADER or a field group containing fields which are also in the field group HEADER (see also INSERT for field groups). In all other cases, a runtime error occurs.

If you use one of the additions 2 to 5 before BY, it applies to all fields of the sort key by default. You can also specify these additions after each individual sort field f1, f2, ..., fn. For each key field, this defines an individual sort formula which overrides the default.

Addition 2

... ASCENDING

Effect

Sorts in ascending order. This is also the default if no sort order is specified directly after SORT. For this reason, it is not necessary to specify ASCENDING explicitly as the default sort order.

With the addition BY, you can also specify ASCENDING directly after a sort field to define ascending order explicitly as the sort sequence for this field.

Addition 3

... DESCENDING

Effect

Sorts in descending order. If the addition comes right after SORT, DESCENDING is taken as the default for all fields of the sort key.

With the addition BY, you can also specify DESCENDING directly after a sort field.

Addition 4

... AS TEXT

Effect

Text fields are sorted appropriate to the locale. This means that the relative order of characters is defined according to the text environment being used.

When an internal session is opened (in other words, when a roll area is opened), the text environment is automatically set to the active user's language as specified in the user master record. If necessary, however, you can change the text environment explicitly in your program by using a SET-LOCALE statement.

If the addition comes directly after SORT, locale-specific rules are used for all fields of the sort key where the type is C or W. After the sort, the sequence of entries usually does not match the sequence which results otherwise, without using the addition AS TEXT, i.e. with binary sorting.

With the addition BY, you can also specify AS TEXT directly after a sort criterion fi. If fi is a field, it must be of type C or W, or have a structured type, but if fi is a field group, the addition applies as though it had been specified individually for each field in the group. In sort fields with structured types, AS TEXT only applies to the subcomponents with type C or W.

In case of an invalid character, a SYSLOG message is written, and the respective record is inserted at the end.

Note

Please keep the rules for site-specific sorting in mind.

Example

Sort a list of orders:

DATA: ONR(7)       TYPE C,
      DATE         TYPE D,
      POSITION(3)  TYPE N,
      CUSTOMER(16) TYPE C,
      PNR(5)       TYPE N,
      NAME(10)     TYPE C,
      UNITS        TYPE I,
      ORDERS       TYPE I.
FIELD-GROUPS:
      HEADER, ORDER, PRODUCT, DATE_FIRST.

INSERT ONR DATE POSITION INTO HEADER.
INSERT CUSTOMER          INTO ORDER.
INSERT PNR NAME UNITS    INTO PRODUCT.
INSERT DATE ONR POSITION INTO DATE_FIRST.

ONR      = 'GF00012'.  DATE     = '19921224'.
POSITION = '000'.      CUSTOMER = 'Good friend (2.)'.
EXTRACT ORDER.
ADD 1 TO POSITION.
PNR = '12345'.  NAME = 'Screw'.  UNITS = 100.
EXTRACT PRODUCT.
ADD 1 TO POSITION.
PNR = '23456'.  NAME = 'Nail'.   UNITS = 200.
EXTRACT PRODUCT.

ONR      = 'MM00034'.  DATE     = '19920401'.
POSITION = '000'.      CUSTOMER = 'Moneymaker'.
EXTRACT ORDER.
ADD 1 TO POSITION.
PNR = '23456'.  NAME = 'Nail'.   UNITS = 300.
EXTRACT PRODUCT.
ADD 1 TO POSITION.
PNR = '34567'.  NAME = 'Hammer'. UNITS = 4.
EXTRACT PRODUCT.

ONR      = 'GF00011'.  DATE     = '19921224'.
POSITION = '000'.      CUSTOMER = 'Good friend (1.)'.
EXTRACT ORDER.
ADD 1 TO POSITION.
PNR = '34567'.  NAME = 'Hammer'. UNITS = 5.
EXTRACT PRODUCT.

SORT BY DATE_FIRST.
LOOP.
  AT ORDER.
    WRITE: /, / DATE, ONR, POSITION,
                CUSTOMER, 'ordered:'.
  ENDAT.
  AT PRODUCT.
    WRITE: /    DATE, ONR, POSITION,
                PNR, NAME, UNITS.
  ENDAT.
ENDLOOP.

This generates the following list:

01041992 MM00034 000 Moneymaker       ordered:
01041992 MM00034 001 23456 Nail              300
01041992 MM00034 002 34567 Hammer              4

24121992 GF00011 000 Good friend (1.) ordered:
24121992 GF00011 001 34567 Hammer              5

24121992 GF00012 000 Good friend (2.) ordered:
24121992 GF00012 001 12345 Screw             100
24121992 GF00012 002 23456 Nail              200

Example

Binary and locale-specific sorting of a name list:

DATA: NAME(10)   TYPE C,
      AGE        TYPE I,
      COUNTRY(3) TYPE C,
      NR(5)      TYPE N.
FIELD-GROUPS:
      HEADER, PERSON.

INSERT NAME COUNTRY     INTO HEADER.
INSERT NAME COUNTRY AGE INTO PERSON.

NAME    = 'Muller'. AGE = 22. COUNTRY = 'USA'.
EXTRACT PERSON.
NAME    = 'Moller'. AGE = 25. COUNTRY = 'FRG'.
EXTRACT PERSON.
NAME    = 'Möller'. AGE = 22. COUNTRY = 'USA'.
EXTRACT PERSON.
NAME    = 'Miller'. AGE = 23. COUNTRY = 'USA'.
EXTRACT PERSON.

SORT BY NAME.
LOOP.
  WRITE: / NAME, AGE, COUNTRY.
ENDLOOP.

This generates the following binary sorted list:

Miller             23  USA
Moller             25  FRG
Muller             22  USA
Möller             22  USA


If, for example, you apply German sort rules where the umlaut comes directly after the letter 'o' in the sort, the data record beginning with 'Möller' would not be in the right place in this sequence. It should come second.

Provided a German-language locale is set (e.g. sorting is according to official German grammatical rules, see also SET LOCALE), you can sort the names according to German rules as follows:

SORT BY NAME AS TEXT.

This produces the following output:

Miller             23  USA
Moller             25  FRG
Möller             22  USA
Muller             22  USA

Addition 5

... STABLE

Effect

The sort is stable, that is, the relative order of entries that have the same SORT key is not changed in the sort.

Unlike in additions 2 to 4, you cannot specify this addition directly following a sort field.

Notes

General:

  1. The number of sort fields is restricted to 50.

  2. The sort process is only stable when you use the STABLE option. Otherwise, a predefined sequence of fields used to sort a list is not usually retained.

  3. Any field symbol after the BY that is not assigned is ignored. If a field symbol is assigned, but does not point to the field group HEADER, a runtime error results.


  4. The SORT statement sorts fields with the contents HEX 00 before all other fields, regardless of whether you sort in ascending or descending order. When you work with logical databases, the system fills all of the fields of the work area for the current GET statement with the value HEX 00 at the end of a hierarchy level. If you specify a field list in the GET statement, any field not specified in the list is filled with HEX 00 during the GET event.
  5. If you have to sort an extract dataset several times according to locale-specific rules ( AS TEXT), you are recommended to include an additional field in the sort key where you can explicitly store the data formatted appropriate to the locale with the CONVERT TEXT ... INTO SORTABLE CODE statement. If you perform a binary sort on this data, the original data is also implicitly sorted according to the locale.

  6. Extracts larger than 500 KB are stored in the file system. You can define the directory where this file is held with the SAP profile parameter DIR_SORTTMP. By default, the SAP data directory (SAP profile parameter DIR_DATA) is used. Extracts stored in the file system are sorted physically. If the extract is larger than 8 MB, the system uses an external auxiliary file. You can specify the directory in which the system creates this file using the SAP profile parameter DIR_SORTTMP. The default directory is the SAP data directory (SAP profile parameter DIR_DATA).


Notes

Performance:

  1. The runtime required to sort an extract dataset increases with the number of entries and the length of the sort key.


  2. The runtime increases if you use a stable sort.


  3. Physical sorting decreases the runtime required for subsequent sequential processing of the dataset.


Exceptions

Non-Catchable Exceptions

Related

SORT itab

Additional help

Sorting Extracts