SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-86
BETWEEN Predicate
If a clause specifies a column in a key BETWEEN expr2 and expr3, expr3 must be
greater than expr2 even if the column is specified as DESCENDING within its table
definition.
Examples of BETWEEN
•
This predicate is true if the total price of the units in inventory is in the range from
$1,000 to $10,000:
qty_on_hand * price
BETWEEN 1000.00 AND 10000.00
•
This predicate is true if the part cost is less than $5 or more than $800:
partcost NOT BETWEEN 5.00 AND 800.00
•
This BETWEEN predicate selects the part number 6400:
SELECT * FROM partsupp
WHERE partnum BETWEEN 6400 AND 6700
AND partcost > 300.00 SERIALIZABLE ACCESS;
Part/Num Supp/Num Part/Cost Qty/Rec
-------- -------- ------------ ----------
6400 1 390.00 50
6401 2 500.00 20
6401 3 480.00 38
--- 3 row(s) selected.
•
Find names between Jody Selby and Gene Wright:
(last_name, first_name) BETWEEN
('SELBY', 'JODY') AND ('WRIGHT', 'GENE')
The name Barbara Swift would meet the criteria; the name Mike Wright would not.
SELECT empnum, first_name, last_name
FROM persnl.employee
WHERE (last_name, first_name) BETWEEN
('SELBY', 'JODY') AND ('WRIGHT', 'GENE');
EMPNUM FIRST_NAME LAST_NAME
------ --------------- --------------------
43 PAUL WINTER
72 GLENN THOMAS
74 JOHN WALKER
...
--- 15 row(s) selected.