SORT

SORT sequences records within a report file in a user-specified order. A subsequent DISPLAY command displays the report lines in the specified sequence.

Syntax:

►►─── SORT ───────────────────────────────────────────────────────────────────►

 ►─┬───────────────────────────────────────────────────────────────┬──────────►
   └─ REPort ─┬─────┬─ report-name ─┬────────────────────────────┬─┘
              └─ = ─┘               └─ USEr ─┬─────┬─ user-name ─┘
                                             └─ = ─┘

 ►─┬──────────┬───────────────────────────────────────────────────────────────►
   └─ UNIque ─┘

 ►─┬──────────┬───────────────────────────────────────────────────────────────►
   ├─ EQUals ─┤
   └─ EQ ─────┘

 ►─┬─────────────────────────────────────────────────────────────┬────────────►
   └─ ALTseq CODe ─┬─────┬─┬─ (sort-sequence) ─────────────────┬─┘
                   └─ = ─┘ └─ code-table-name ─┬─────────────┬─┘
                                               └─ (version) ─┘

 ►─── ON field-reference ─────────────────────────────────────────────────────►

 ►─┬──────────────────────────────────────────────────────────────┬───────────►
   └─ withIN ─┬─┬────────────┬─ record-name ─┬────────────────┬─┬─┘
              │ └─ view-id. ─┘               └─ (occurrence) ─┘ │
              └─ REPOrt ────────────────────────────────────────┘

 ►─┬──────┬───────────────────────────────────────────────────────────────────►
   └─ AQ ─┘

 ►─┬────────────────────────────────┬─────────────────────────────────────────►
   └─ in ─┬─ ASCending ◄ ─┬─ order ─┘
          └─ DEScending ──┘

 ►─┬──────────────────────────────────────┬───────────────────────────────────►◄
   │ ┌──────────────────────────────────┐ │
   └─▼- minor-sort-field-specification ─┴─┘

Expansion of minor-sort-field-specification

►►─── AND ON field-reference ─────────────────────────────────────────────────►

 ►─┬──────────────────────────────────────────────────────────────┬───────────►
   └─ withIN ─┬─┬────────────┬─ record-name ─┬────────────────┬─┬─┘
              │ └─ view-id. ─┘               └─ (occurrence) ─┘ │
              └─ REPOrt ────────────────────────────────────────┘

 ►─┬──────┬───────────────────────────────────────────────────────────────────►
   └─ AQ ─┘

 ►─┬────────────────────────────────┬─────────────────────────────────────────►◄
   └─ in ─┬─ ASCending ◄ ─┬─ order ─┘
          └─ DEScending ──┘

Parameters:

REPORT= report-name

Identifies the saved report to be sorted. If report-name isn't specified, the default is the current report.

USER= user-name

Specifies the user ID of the user who owns the report. If user-name isn't specified, the report retrieved is from the current user's directory.

UNIQUE

Eliminates extra report lines containing duplicate sort keys. Specify UNIQUE after the SORT verb and before the sort field reference.

Note that specifying UNSORT after issuing UNIQUE does not restore the lost lines.

EQUALS

Maintains the original order of report records with duplicate sort keys. Specify EQUALS after the SORT verb and before the sort field reference.

ALTSEQ CODE

Specifies an alternative sort sequence. ALTSEQ follows the SORT statement and appears before the first sort field.

(sort-sequence)—The alternative sort sequence. The list consists of encode/decode pairs:
- The encode value represents the hexadecimal value of the data in the database.
- The decode value represents the hexadecimal value with which to replace the data.
The sort-sequence list must be enclosed in parentheses.
code-table-name—A code table stored in the data dictionary. The code table is defined in IDD using DDDL syntax. Code-table-name cannot be enclosed in parentheses. The table version number must be enclosed in parentheses. Define the code table with the following options:
- TYPE=CODE
- SEARCH=LINEAR
- DATA=ALPHANUMERIC
- DUPLICATES NOT ALLOWED
version—The version number of the code table. If version isn't specified, it defaults to the highest existing version number of the named code table.

ON field-reference

Identifies the report field used to resequence a record. Both elementary and computed fields can be specified.

WITHIN record-name (occurrence)

. Specifies the scope of the sort. If this clause is omitted, scope defaults to WITHIN REPORT. The scope of the sort is:

Record-name (occurrence)—Sequencing is restarted for each new occurrence of the named record. WITHIN record-name has no effect if the record name occurs lower on the path than the record that contains the field used for sorting.
When the path that created the report file contains more than one retrieval command for the same record type, use occurrence to identify the desired occurrence. If specified, occurrence must be enclosed in parentheses.

REPORT

Specifies that sequencing continues over the entire report without regard to a change in record occurrence.

AQ

Flags fields requiring the alternate collating (sequence) change. AQ can appear anywhere after the field name and before the AND for the next field. AQ cannot interrupt a SCOPE clause; for example, ON DEPT-ID IN AQ REPORT.

IN ASCENDING/DESCENDING ORDER

Specifies whether records are sequenced in ascending or descending order. The default is ASCENDING.

minor-sort-field-specification

Specifies a lower level (minor) sort field. Fields named by field-reference are sorted within the previously sorted field. The scope and order of the lower level field can be specified as follows:

WITHIN record-name (occurrence)/REPORT specifies the scope of the sequencing.
If you specify record-name, sequencing restarts each time a new occurrence of the named record is encountered. The optional occurrence parameter identifies the occurrence when the path that created the report file contains more than one retrieval command for the same record. If specified, occurrence must be enclosed in parentheses.
IN ASCENDING/DESCENDING ORDER—Fields are sequenced in ascending or descending order. The default is ASCENDING.

Considerations:

You can specify up to 22 fields in one SORT command.

The report file can be returned to its original sequence at any time by using the UNSORT command described later in this chapter.

Note that the UNIQUE parameter permanently removes lines from the report.

When running batch you can use the OLQ internal sort or the sort facility of your operating facility.

Examples:

The following examples illustrate the use of the SORT command to arrange records in a report file, based on the SELECT statement shown below:

select dept-name-0410,emp-id-0415
 from department, employee
 where dept-employee

Sort ascending

In the following example, the report is sorted in ascending alphabetical order by department name:

sort on dept-name-0410 ! display DEPARTMENT/EMPLOYEE REPORT mm/dd/yy DEPT-NAME-0410 EMP-ID-0415 ACCOUNTING AND PAYROLL 67 ACCOUNTING AND PAYROLL 11 ACCOUNTING AND PAYROLL 101 ACCOUNTING AND PAYROLL 106 ACCOUNTING AND PAYROLL 69 ACCOUNTING AND PAYROLL 100 BLUE SKIES 371 BLUE SKIES 321 BLUE SKIES 366 BRAINSTORMING 467 BRAINSTORMING 341 BRAINSTORMING 458 BRAINSTORMING 334 BRAINSTORMING 457 - 1 -

Sort on ... and on

The following SORT command sorts the records in the report by employee last name within department name; both sort fields are sorted in ascending order:

sort on dept-name-0410 and on emp-id-0415 ! display DEPARTMENT/EMPLOYEE REPORT 09/22/99 DEPT-NAME-0410 EMP-ID-0415 ACCOUNTING AND PAYROLL 11 ACCOUNTING AND PAYROLL 67 ACCOUNTING AND PAYROLL 69 ACCOUNTING AND PAYROLL 100 ACCOUNTING AND PAYROLL 101 ACCOUNTING AND PAYROLL 106 BLUE SKIES 321 BLUE SKIES 366 BLUE SKIES 371 BRAINSTORMING 301 BRAINSTORMING 334 BRAINSTORMING 341 BRAINSTORMING 457 BRAINSTORMING 458 - 1 -

Sort descending

In the final example, records are sorted in descending order by EMP-ID-0415 within the scope of the entire report:

sort on emp-id-0415 in descending order ! display cols=2,1 DEPARTMENT/EMPLOYEE REPORT 09/22/99 EMP-ID-0415 DEPT-NAME-0410 9999 PUBLIC RELATIONS 8683 PERSONNEL 479 THERMOREGULATION 476 PUBLIC RELATIONS 472 EXECUTIVE ADMINISTRATION 471 EXECUTIVE ADMINISTRATION 469 THERMOREGULATION 467 BRAINSTORMING 466 BRAINSTORMING 458 BRAINSTORMING 457 BRAINSTORMING 371 BLUE SKIES 366 BLUE SKIES 355 THERMOREGULATION - 1 -

For more information:

Batch Processing

Global Syntax