BETWEEN clause

Returns items with a range of values.

Synopsis

[[WITH] field] BETWEEN "start" "end"

WHEN field BETWEEN "start" "end"

Description

The BETWEEN clause returns those values of field between start and end, inclusive of both start and end. This range is specified as an ascending collation sequence. Neither start nor end have to be existing field values. Comparisons are case-sensitive.

If start and end are the same value, that value (if present) is returned. If start is higher in the collation sequence than end, no values are returned and a [401] “No items present” message is displayed.

BETWEEN cannot use "" (null) as a start or end value. If start or end is "" (null) no values are returned and a [401] “No items present” message is displayed. This handling of null differs from other conditional operations such as AFTER, >, and >= conditionals, which use "" (null) to indicate the beginning of the collation sequence, or = "" , which returns those fields that have no value (null).

BETWEEN can be used in a WITH clause or a WHEN clause. If you omit the WITH keyword, the BETWEEN test defaults to an implicit WITH @ID clause, as shown in the following examples.

The following example tests for a range of F4 values, returning the @ID of the selected records.

LIST VOC WITH F4 BETWEEN "A" "M"

The following example tests for a range of @ID values, returning the @ID and F4 values of the selected records:

LIST VOC F4 BETWEEN "A" "M"

Examples

The following example lists the values between “A” and “AM” (inclusive):

LIST VOC WITH @ID BETWEEN "A" "AM"

It returns A ABORT AFTER ALL ALL.MATCH.

The following example uses “ ” (blank) to approximate the beginning of the collation sequence:

LIST VOC WITH @ID BETWEEN " " "AM"

It returns # &COMO& &PH& &SAVEDLISTS& ; @CMQLOPTS A ABORT AFTER ALL ALL.MATCH.