ORDER |
Order By is used to set the browsing index of a table, even to create a new index.
SYNTAX 1
... Order By Key key_id ['('exp_ind')'] [ Asc | Desc ] ...
SYNTAX 2
... Order By Key key_id = key_expr_list ...
SYNTAX 3
... Order Bykey_expr_list ...
SYNTAX 4
... Order With Key key_id = key_string ...
SYNTAX 5
... Order With key_string ...
|
Element |
Description |
Restrictions |
|
id_clé |
variable_name that designates a key. |
The key must exist (syntax 1) |
|
exp_ind |
Expression entière du nombre de parties de clés utilisées. Par défaut, toute la clé est utilisée. |
0 <=exp_ind <=number of key components (at most 8). |
|
liste_expr_clé |
List ofkey_expr separated by ';' |
Au plus 8 sous-clés |
|
key_string |
Char type expression whose result is a list of key_expr separated by ';' |
Au plus 8 sous-clés |
|
expr_clé |
Name of the sub-keys expressed in one of the following forms: |
|
|
sub_key |
expression concerning |
None following operator or function (see Where ) |
# Choice of a recording in the ENG abbreviation file, among
# those meeting the entry criterion and sorted in the ascending order of
# the first part of the NUMENG key.
Choose [ENG] Where evalue(CRITERE) Order By NUMENG(1) Desc Mask [SEN]
# Link between the stock file (abbrevation STK) and the
# product file (abbreviation PRO) sorted by "CASIER" and by "ARTICLE".
Link [STK] With [F:PRO]CODPRO = [F:STK]ARTICLE As [LSTK]
& Order By Key CLE = CASIER; ARTICLE
# Browsing of the file with TFI abbreviation according to the RANG and the NOM
Filter [TFI] Where SPECIF = 3 & RANG > 0
& Order By RANG Asc; NOM Asc
For [TFI] : Call COPIE([F:TFI]NOM): Next
Filter [TFI]
# Same thing as the two previous examples, but written with a character string
Link [STK] With [F:PRO]CODPRO = [F:STK]ARTICLE As [LSTK]
& Order WithKey CLE = "CASIER; ARTICLE"
# Same thing as the two previous examples, but written with a character string
Filter [TFI] Where SPECIF = 3 & RANG > 0
& Order With"RANG Asc; NOM Asc"
Order By is used with the File, Link, Filter and Choose instructions to set the browsing key of a table (or a group of tables, in the case of a Link order) or else to create a new key. It is prohibited on the System type files.
This key becomes the only one to be known for this table and then it masks all the others until the next Order By clause.
Order With is used instead of Order By when the list of elements making up the sort is contained in the character string.
Declaration of a new key
- A new key will be created with syntaxes 2, 3, 4 and 5. By using the 3rd or 5th syntax, no name is given to the key which is accepted by most of the read/write orders.
- A new key will always be of type 'With Homonyms'.
- Keys are limited to 8 parts and their size is 128 bytes.
- The number of keys is limited to 32 by table. The new keys created by Order by are added to those defined by the parameterization. By performing the following sequence of instructions:
File FICH ... Order by ...
Filter [FICH] ... Order by ...
Choose [Fich] ... Order by ... , 3 additional keys are created.
- By default, the browsing will take place in the ascending order of each key part, unless the Desc clause (for Descending ) has been mentioned. When applying Desc to a pre-existing key (syntax 1), the "sense" of each key part is modified: the parts defined as ascending become descending and conversely.
The declaration of an additional key costs time and money, irrespective of the datrabase type. And all the more since the number of involved recordings is big. It is thus recommended to:
It is possible to directly use fields of the file or use them to calculate expressions. In that case the numerical and alphanumerical functions are limited like for the Where clause.
By specifying the key index (syntax 1) it is possible to limit the recordings to those that cause this key part to be modified, which enables the homonyms to be ignored. This is particularly interesting with the Choose order.
If a new key is created (syntax 2) it will not be possible to use it to redefine it. For instance:
File FIC1 [FIC1] Order By Key NEW = ALPHA; DCB ... then Filter [FIC1] Order By Key NEW Desc will generate an error "NEW: Key does not exist in this table upon execution.
The temporary files created when defining a new key on a C-isam table are stored in the /tmp directory, unless the TMPDIR environment variable contains the name of another directory. It is not possible to modify them. They are only accessible by the current process and they are physically deleted at the end of their use or at the end of the process.
They use two additional Unix channels (these channels are not accounted for in [S]adxmso ).
Since the Order By clause masks all the keys except one, some variables of class [G] that are associated wuth the table are modified according to the grid below.
|
Name |
Dimension |
Value |
|
currind |
(0) unchanged |
1 |
|
keylen |
(0) |
recalculated |
|
keyname |
(0) |
(syntax 3) |
|
keyuniq |
(0) |
0 |
|
nbind |
(0) unchanged |
1 |
In the case of a set of tables linked by the Link instruction, only the class [G] associated with this set is impacted, the table keys remain usable.
The Sorta instruction used to sort variables also uses Order By but with entirely different specifications.
|
Error |
Description |
|
PACLE (21) |
Key does not exist (OrderBy : 1st syntax). |
|
ERDIM (55) |
Key length redefined by "OrderBy ..." > 128 |
FILE - LINK - FILTER - CHOOSE - WHERE - CURRIND - ADXMTO
|
Copyright © Sage 1999 - 2007 |