|
|
|
|
|
|
|
|
|
|
version 3
ORDER BY ({table}{; field{; > or <{; field2; > or <2; ...; fieldN; > or <N}{; *}}})
| Parameter | Type | Description | |
| table | Table |  | Table for which to order selected records, or |
| Default table, if omitted | |||
| field | Field | | Field on which to set the order for each level |
| > or < | | Ordering direction for each level: | |
| > to order in ascending order, or | |||
| < to order in descending order | |||
| * | | Continue order flag |
Description
ORDER BY sorts (reorders) the records of the current selection of table for the current process. After the sort has been completed, the new first record of the selection becomes the current record.
If you omit the table parameter, the command applies to the default table. If no default table has been set, an error occurs.
If you do not specify the field, the > or < or the * parameters, ORDER BY displays the Order By editor for table. The User environment's Order By editor is shown here:
For more information about using the Order By editor, refer to the 4th Dimension User Reference manual.
The user builds the sort, then clicks the Sort button to perform the sort. If the sort is performed without interruption, the OK variable is set to 1. If the user clicks Cancel, the ORDER BY terminates with no sort actually performed, and sets the OK variable to 0 (zero).
Examples
1. The following line displays the Order By editor for the [Products] table:
ORDER BY([Products])
2. The following line displays the Order By editor for the default table (if it has been set):
ORDER BY
If you specify the field and > or < parameters, the standard Order By editor is not presented and the sort is defined programmatically. You can sort the selection on one level or on several levels. For each sort level, you specify a field in field and the sorting order in > or <. If you pass the "greater than" symbol (>), the order is ascending. If you pass the "less than" symbol (<), the order is descending.
Examples
3. The following line orders the selection of [Products] by name in ascending order:
ORDER BY([Products];[Products]Name;>)
4. The following line orders the selection of [Products] by name in descending order:
ORDER BY([Products];[Products]Name;<)
5. The following line orders the selection of [Products] by type and price in ascending order for both levels:
ORDER BY([Products];[Products]Type;>;[Products]Price;>)
6. The following line orders the selection of [Products] by type and price in descending order for both levels:
ORDER BY([Products];[Products]Type;<;[Products]Price;<)
7. The following line orders the selection of [Products] by type in ascending order and by price in descending order:
ORDER BY([Products];[Products]Type;>;[Products]Price;<)
8. The following line orders the selection of [Products] by type in descending order and by price in ascending order:
ORDER BY([Products];[Products]Type;<;[Products]Price;>)
If you omit the sorting order parameter > or <, ascending order is the default.
Example
9. The following line orders the selection of [Products] by name in ascending order:
ORDER BY([Products];[Products]Name)
If only one field is specified (one level sort) and it is indexed, the index is used for the order. If the field is not indexed or if there is more than one field, the order is performed sequentially. The field may belong to the (selection's) table being reordered or to a One table related to table with an automatic relation. (Remember, the table to which ORDER BY is applied must be the Many table.) In this case, the sort is always sequential.
Examples
10. The following line performs an indexed sort if [Products]Name is indexed:
ORDER BY([Products];[Products]Name;>)
11. The following line performs a sequential sort, whether or not the fields are indexed:
ORDER BY([Products];[Products]Type;>;[Products]Price;>)
12. The following line performs a sequential sort using a related field:
ORDER BY([Invoices];[Companies]Name;>) ` Invoices are sorted alphabetically on the Company name field
For multiple sorts (sorts on multiple fields), you can call ORDER BY as many times as necessary and specify the optional * parameter, except for the last ORDER BY call, which starts the actual sort operation. This feature is useful for multiple sorts management in customized user interfaces.
Warning: with this syntax, you can pass only one sort level (field) per ORDER BY call.
Example
13. In an Output form displayed in Custom menus environment, you allow the users to order a column in ascending order by simply clicking in the column header.
If the user holds the Shift key down while clicking in other column headers, the sort is performed on several levels:
Each column header contains a highlight button attached with the following object method:
MULTILEVEL (->[CDs]Title) `Title column header button
Each button calls the MULTILEVEL project method with a pointer to the corresponding column field. The MULTILEVEL project method is the following:
` MULTILEVEL Project Method
` MULTILEVEL (Pointer)
` MULTILEVEL (->[Table]Field)
C_POINTER($1) `Sort level (field)
C_LONGINT($lLevelNb)
`Getting sorting levels
If (Not(Shift down)) `Simple sort (one-level)
ARRAY POINTER(aPtrSortField;1)
aPtrSortField{1}:=$1
Else
$lLevelNb:=Find in array(aPtrSortField;$1) `Is this field already sorted?
If ($lLevelNb<0) `If not
INSERT ELEMENT(aPtrSortField;Size of array(aPtrSortField)+1;1)
aPtrSortField{Size of array(aPtrSortField)}:=$1
End if
End if
`Performing the sort
$lLevelNb:=Size of array(aPtrSortField)
If ($lLevelNb>0) `There is at least one order level
For ($i;1;$lLevelNb)
ORDER BY([CDs];(aPtrSortField{$i})->;>;*) `Building sort definition
End for
ORDER BY([CDs]) `No * ends the sort definition and starts the actual sort operation
End if
No matter what way a sort has been defined, if the actual sort operation is going to take some time to be performed, 4th Dimension automatically displays a message containing a progress thermometer. These messages can be turned on and off by using the commands MESSAGES ON and MESSAGES OFF. If the progress thermometer is displayed, the user can click the Stop button to interrupt the sort. If the sort is completed, OK is set to 1. Otherwise, if the sort is interrupted, OK is set to 0 (zero).
See Also
|
|
|
|
|
|
|
|
|
|