Returns items with a range of values.
[[WITH] field] BETWEEN "start" "end"
WHEN field BETWEEN "start" "end"
clause returns those values of field
, inclusive of both start
. This range is specified as an ascending collation sequence. Neither start
have to be existing field
values. Comparisons are case-sensitive.
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  No items present message is displayed.
cannot use ""
(null) as a start
value. If start
(null) no values are returned and a  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).
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"
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.
© 1997-2019 InterSystems Corporation, Cambridge, MA