The Selection Clause

The Selection Clause

Top  Previous  Next

 

A selection clause may be provided to specify criteria governing which records are processed by the command. If omitted, all records are processed. Selection clauses can be used with all query processor verbs

 

The selection clause is described in detail under the WITH and WHEN keywords. The records to be processed by a query can also be specified by use of a select list. The FROM keyword can be used to specify the list to be used. If this is not present and the default list (list zero) is active, this is used automatically.

 

The performance of queries against large files can be improved dramatically by use of alternate key indices. These are index files that relate a particular value of a data field or virtual attribute to the ids of records that have that value. Alternate key indices are created in a two step operation using the CREATE.INDEX  and BUILD.INDEX commands. Once an index has been built, it is maintained automatically by QM and is used by the query processor whenever it is advantageous to do so.

 

Ranges of values can also be satisfied using indices if the upper and lower limits are defined in the first two conditional elements. For example,

LIST STOCK WITH QOH > 3 AND QOH < 10 AND SUPPLIER = 27

will use an index on the QOH field to access the data whereas

LIST STOCK WITH QOH > 3 AND SUPPLIER = 27 AND QOH < 10

will not. Use of unnecessary brackets may also defeat the indexing system. For example,

LIST STOCK WITH QOH > 3 AND (QOH < 10 AND SUPPLIER = 27)

will not use the index as the second conditional element is not a simple item.

 

 

Selection clause comparisons are case sensitive by default. Case insensitivity can be applied by including the NO.CASE qualifier after the relational operator or by use of the QUERY.NO.CASE mode of the OPTION command. See Alternate Key Indices for a discussion of why a case sensitive index cannot be used to resolve a case insensitive selection clause element or vice versa.

 

 

The PICK.IMPLIED.EQ mode of the OPTION command can be used to select Pick style behaviour where a field name followed by a literal value enclosed in double quotes has an implied equals operator. Thus

LIST CLIENTS WITH CUST.NO "1234" "5678"

is equivalent to

LIST CLIENTS WITH CUST.NO = "1234" "5678"

Without this option, the semantics of the query are such that the two literal values are treated as record ids and the selection element restricts processing to records in which the CUST.NO field is not empty.

 

 

See also:

Qualified display clauses