Syntax
SELECT [FROM] fname
[WHERE condition]
[SORTED [BY] sortlist] ;
Fname is the internal file name of the file from which to get records. The WHERE clause is optional and if omitted is the same as:
WHERE $TRUE
The SORTED clause is optional and specifies in what order the records are to be put. The keyword SORTED may be abbreviated to SORT. Sortlist is defined as:
ASCENDING
fldref [ INCREASING ] [, ...]
DESCENDING
DECREASING
Fldref should be a reference to a field in fname. Each reference can be a field name or, if the field is an array, a field name followed by a subscript. The following table shows acceptable abbreviations and what sorting order will be done:
Order Abbreviation Sorting order
ASCENDING ASC sort field in ascending order
INCREASING INCR sort field in ascending order
DESCENDING DESC sort field in descending order
DECREASING DECR sort field in descending order
The sorting order parameter is optional and if omitted is assumed to be ASCENDING. Using ASCENDING (ASC) or INCREASING (INCR) will not change how the records are actually sorted. In either case, the sorting will be done in increasing order. The same is true for DESCENDING (DESC) and DECREASING (DECR) except that the sorting is done in decreasing order.
Any nonunique field references will be resolved by using fname.
Purpose
The SELECT statement is used to prepare a file for processing. It involves two steps, the second of which may be omitted. The first step is the selection in which the file is scanned and some of the records are selected for use based upon some condition. The second step is the sort in which the selected records are sorted in some order specified by the sort clause. The final output from the select statement is then used by the FOR statement or the READ statement.
The WHERE clause specifies the condition for record selection. If this clause is omitted, all the records in the file will be selected. For more advanced selection conditions, see the section on record selection at the end of this chapter.
The SORTED clause specifies how the selected records are to be sorted. If this clause is omitted, the records will be left in the order in which they are selected from the file. The SORTED key word can be abbreviated as SORT.
The report writer supports two types of sorting: numeric and ascii. Ascii sorting sorts fields based on the ASCII values of the characters in the fields. Numeric sorting sorts fields based on their numeric values. The data type of the fields being sorted determines the type of sorting. Fields with types of money, real, or integer are sorted numerically. Fields with types of string or char are sorted as ascii fields. Fields with types of date or time are sorted numerically by their internal representation. Fields with a data type of boolean are also sorted numerically by their internal representation. In this representation, no values are sorted before yes values.
When a string or char field contains digits, it is still sorted as an ascii field; not a numeric field. Thus, the following string values
101
1
11
9
121
13
sort as
1
101
11
121
13
9
While not in ascending numeric order, these values are in ascending ASCII order. Numeric sorting in a string field can be done by entering the leading zeros. The above example could be done as:
101
001
011
009
121
013
which would sort as
001
009
011
013
101
121
Examples:
SELECT FROM master
WHERE acct_bal GT 100.00
SORTED BY account;
SELECT FROM cust
SORTED BY customer;
SELECT FROM v_details
WHERE voucher EQ v_master.voucher;