BETWEEN predicate

The BETWEEN predicate compares a value with a range of values.


>>-expression--+-----+--BETWEEN--expression--AND--expression---><
               '-NOT-'

If the data types of the operands are not the same, all values are converted to the data type that would result by applying the Rules for result data types.

If the operands of the BETWEEN predicate are strings with different CCSIDs, operands are converted as if the below logically-equivalent search conditions were specified.

The BETWEEN predicate:

   value1 BETWEEN value2 AND value3

is logically equivalent to the search condition:

   value1 >= value2 AND value1 <= value3

The BETWEEN predicate:

   value1 NOT BETWEEN value2 AND value3

is logically equivalent to the search condition:

   NOT(value1 BETWEEN value2 AND value3)

If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the collating sequence.

Examples

            EMPLOYEE.SALARY BETWEEN 20000 AND 40000
 
            SALARY NOT BETWEEN 20000 + :HV1 AND 40000