2-01:Key Order versus Sort Order

Chapter 2-1: Data Dictionary Overview

Key Order versus Sort Order

The key order for a group of records refers to the sequence APPX uses to retrieve records along a key path in an indexed file. For example, if you scan through customer records within an APPX Customer input process, the customer records are presented in key order. Key order can be defined in ascending or descending sequence.

The sort order refers to the sequence an APPX query process arranges records in for use by a subsequent output, input, or update process. Sort order, also known as APPX’s collating sequence, can be defined in ascending or descending sequence as well.

The principal differences between the key order and sort order sequences are as follows:

· Sort order is language-dependent.

· Sort order combines uppercase and lowercase alpha characters (i.e., AaBbCcDd...). Key order treats uppercase and lowercase separately (i.e., ABCD...abcd...).

· Sort order always presents numeric field values in sequence (negative to positive, if ascending, or positive to negative, if descending). Key order may not be sequential if there are both positive and negative values.

· Sort order uses the the alphanumeric value of token fields. Key order uses the numeric token ID.

For date and logic fields, APPX’s sort order is identical to its key order (assuming both are defined as ascending or both descending). For other fields, however, the two sequences are not always identical. The distinction is important because an identical group of records can be sequenced differently by two APPX processes if one process uses a key path and the other uses the sort order established by a query process.

It is also important to understand that sort fields are not restricted to key fields. In other words, a field does not have to be a key to be used as a sort field in a query process. (Sort fields are a common example of the overuse of key fields.) An APPX sort creates the equivalent of a temporary key path (for example, it is only available during the production of a report), which is based on the sort field. If a sort field is defined as a key, APPX must constantly maintain the key path online. If a field is not a key, APPX only builds a key path when it is required by a sort.

While APPX’s sort order combines upper and lowercase alpha characters, the key order retrieval for alphanumeric fields separates the series of uppercase alpha characters from the series of lowercase alpha characters.

Whereas numeric fields are always sorted in numeric sequence (say, -2, -1, 0, 1, 2, for ascending), the numeric key order can vary depending upon storage type. For instance, the key order for the same sample values in a packed decimal numeric field would be 0, 1, -1, 2, -2.

If a field is defined as a token, the key order retrieval is always in numeric sequence by token ID. Keys always operate on the actual data stored in a file which, in this case, is the token ID. The sort order established by a query process, however, is based on the corresponding alphanumeric value of the token field, not the token ID.

APPX Application Design Manual (01/13/03)