ORDER

Contents


Order By is used to set the browsing index of a table, even to create a new index.

Syntax

   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 ...

Parameters

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 [ Asc ]
sub_key Desc

 

sub_key

expression concerning
- some variables
- fields of the file

None following operator or function (see Where )

Examples

   # 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"

Description and comments

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.

Remarks

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.

Associated errors

Error

Description

PACLE (21)

Key does not exist (OrderBy : 1st syntax).

ERDIM (55)

Key length redefined by "OrderBy ..." > 128

Associated keywords

FILE - LINK - FILTER - CHOOSE - WHERE - CURRIND - ADXMTO


CONTENTS

Copyright © Sage 1999 - 2007