| Reports / The report routine | |
Forces a sort order of unsorted data rows in reports.
ORDER [ EXTERNAL ] BY report-variable [ DESC | ASC ] [,...]
When grouping rows in a report, values that the report definition receives from the report driver are significant in determining how BEFORE GROUP OF or AFTER GROUP OF control blocks will process the data in the formatted report output.
The ORDER BY section defines how the variables of the input records are to be sorted. It is required if the report driver does not send sorted data to the report. The specified sort order determines the order in which the runtime system processes any GROUP OF control blocks in the FORMAT section.
If you omit the ORDER BY section, the runtime system processes input records in the order received from the report driver and processes any GROUP OF control blocks in their order of appearance in the FORMAT section. If records are not sorted in the report driver , the GROUP OF control blocks might be executed at random intervals (that is, after any input record) because unsorted values tend to change from record to record.
If you specify only one variable in the GROUP OF control blocks, and the input records are already sorted in sequence on that variable by the SELECT statement, you do not need to include an ORDER BY section in the report.
Specify ORDER EXTERNAL BY if the input records have already been sorted by the SELECT statement used by the report driver. The list of variables after the keywords ORDER EXTERNAL BY control the execution order of GROUP BY control blocks.
Without the EXTERNAL keyword, the report becomes a two-pass report , meaning that the report engine processes the set of input records twice. During the first pass, the report engine sorts the data and stores the sorted values in a temporary table in the database. During the second pass, it calculates any aggregate values and produces output from data in the temporary files.
With the EXTERNAL keyword, the report engine only needs to make a single pass through the data: it does not need to build the temporary table in the database for sorting the data. However, If the report routine contains aggregations functions such as GROUP PERCENT(*) , the report will become a two-pass report because such aggregation function needs all rows to compute the value.
The DESC or ASC clause defines the sort order.