Sort field values are automatically displayed in ascending
order, beginning with the lowest value and continuing to the highest.
The default sorting sequence varies for operating systems. On z/OS
it is a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields. On
UNIX and Windows it is 0-9, A-Z, a-z for alphanumeric fields; 0-9
for numeric fields.
You have the option of overriding this default and displaying
values in descending order, ranging from the highest value to the
lowest value, by including HIGHEST in the sort phrase.
x
Syntax: How to Specify the Sort Order
{BY|ACROSS} {LOWEST|HIGHEST} sortfield
where:
- LOWEST
- Sorts in ascending order, beginning with the lowest value and
continuing to the highest value (a-z, A-Z, 0-9 for alphanumeric
fields; 0-9 for numeric fields). This option is the default.
- HIGHEST
- Sorts in descending order, beginning with the highest value
and continuing to the lowest value. You can also use TOP as a synonym
for HIGHEST.
- sortfield
- Is the name of the sort field.
Example: Sorting in Ascending Order
The following report request does not
specify a particular sorting order, and so, by default, it lists
salaries ranging from the lowest to the highest.
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL
END
You can specify this same
ascending order explicitly by including LOWEST in the sort phrase.
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY LOWEST CURR_SAL
END
The output is:
Example: Sorting in Descending Order
The following request lists salaries
ranging from the highest to lowest.
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY HIGHEST CURR_SAL
END
The output is:
xSpecifying Your Own Sort Order
Sort field values are automatically
displayed in ascending order, beginning with the lowest value and
continuing to the highest.
You can override the default order and
display values in your own user-defined sorting sequence. To do
this, you need to decide the following:
- Which sort
field values you want to allow. You can specify every sort field value,
or a subset of values. When you issue your report request, only
records containing those values are included in the report.
- The order
in which you want the values to appear. You can specify any order;
for example, you could specify that an A1 sort field containing
a single-letter code be sorted in the order A, Z, B, C, Y...
There are two ways to specify your own
sorting order, depending on whether you are sorting rows with BY,
or sorting columns with ACROSS:
- The BY ROWS
OVER phrase, for defining your own row sort sequence.
- The ACROSS
COLUMNS AND phrase, for defining your own column sort sequence.
x
Syntax: How to Define Your Own Sort Order
BY sortfield AS 'coltitle' ROWS value1 [AS 'text1']
OVER value2 [AS 'text2']
[... OVER valuen [ AS 'textn']]
END
where:
- sortfield
- Is the last BY field in the report.
- coltitle
- Is the column title for the BY field on the report output.
- value1
- Is the sort field value that is first in the sorting sequence.
- AS 'text1'
- Enables you to assign alternate text for the first row, which
replaces the field value in the output. Enclose the text in single
quotation marks.
- value2
- Is the sort field value that is second in the sorting sequence.
- AS 'text2'
- Enables you to assign alternate text for the second row,
which replaces the field value in the output. Enclose the text in
single quotation marks.
- valuen
- Is the sort field value that is last in the sorting sequence.
- AS 'textn'
- Enables you to assign alternate text for the last row, which
replaces the field value in the output. Enclose the text in single
quotation marks.
An alternative syntax is
FOR sortfield value1 OVER value2 [... OVER valuen]
which
uses the row-based reporting phrase FOR, described in Creating Financial Reports With Financial Modeling Language (FML).
x
Reference: Usage Notes for Defining Your Sort Order
- Any sort field
value that you do not specify in the BY ROWS OVER phrase is not
included in the sorting sequence, and does not appear in the report.
- Sort field
values that contain embedded blank spaces should be enclosed in
single quotation marks.
- Any sort field
value that you do specify in the BY ROWS OVER phrase is included
in the report, whether or not there is data.
- The name of
the sort field is not included in the report.
- Each report
request can contain only one BY ROWS OVER phrase. BY ROWS OVER is
not supported with the FOR phrase. For information about the FOR
phrase, see Creating Financial Reports With Financial Modeling Language (FML).
Example: Defining Your Row Sort Order
The
following illustrates how to sort employees by the banks at which
their paychecks are automatically deposited, and how to define your
own label in the sorting sequence for the bank field.
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY BANK_NAME ROWS 'BEST BANK' OVER STATE
OVER ASSOCIATED OVER 'BANK ASSOCIATION'
END
The output is:
x
Syntax: How to Define Column Sort Sequence
ACROSS sortfield COLUMNS value1 AND value2 [... AND valuen]
where:
- sortfield
- Is the name of the sort field.
- value1
- Is the sort field value that is first in the sorting sequence.
- value2
- Is the sort field value that is second in the sorting sequence.
- valuen
- Is the sort field value that is last in the sorting sequence.
x
Reference: Usage Notes for Defining Column Sort Sequence
Example: Defining Column Sort Sequence
The following illustrates how to sum
employee salaries by the bank at which they are automatically deposited,
and to define your own label within the sorting sequence for the bank
field.
TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS BANK_NAME COLUMNS 'BEST BANK' AND STATE
AND ASSOCIATED AND 'BANK ASSOCIATION'
END
The output is:
BANK_NAME
BEST BANK STATE ASSOCIATED BANK ASSOCIATION
-------------------------------------------------------------------------
$29,700.00 $18,480.00 $64,742.00 $27,062.00