ORDER BY section in REPORT
Forces a sort order of unsorted data rows in reports.
Syntax
ORDER [ EXTERNAL ] BY report-variable [ DESC | ASC ] [,...]- report-variable identifies one of the variables passed to the report routine to be used for sorting rows.
Usage
When grouping rows in a report, values that the report definition receives from
the report driver are significant in determining how BEFORE GROUP OF/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.