SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-8
Setting Criteria for Selecting Data
Table 3-1. Search Condition Predicates
Predicate Purpose
Comparison Compares values of two expressions, two sets of expressions, or the value
of an expression and a single value resulting from a subquery. For
example:
PARTNUM = 244
(Part number must equal 244.)
Quantified Compares the value of an expression to all or any of the values of a single
column result from a subquery. For example:
PRICE < ALL (SELECT UNIT_PRICE FROM ODETAIL
WHERE PARTNUM = 5505)
(Price must be less than the unit price in all orders for part number 5505.)
BETWEEN Determines if a value is in the range of two other values, or if a set of
values is in the range of two other sets of values. For example:
PARTNUM BETWEEN 100 AND 500
(Part number must be greater than or equal to 100 and less than or equal
to 500.)
IN Determines if a value is equal to any of the values in a list or in a collection
of values. For example:
PARTNUM IN (100, 120, 150)
(Part number must be 100, 120, or 150.)
LIKE Searches for strings to match a pattern that can contain wild-card
characters percent (%) and underscore (_). For example:
PARTDESC LIKE ’DISK_T%’
(Part description contains DISK followed by exactly one character, followed
by T, followed by zero or more characters.)
EXISTS Determines whether any rows satisfy the conditions of a subquery. For
example:
SELECT * FROM ORDERS O
WHERE EXISTS (SELECT *
FROM ODETAIL OD
WHERE OD.ORDERNUM = O.ORDERNUM
AND PARTNUM = 244);
(Orders must include part number 244.)
NULL Determines whether a column contains a null value. For example:
UNIT_PRICE IS NULL
(Unit price must be null.)