SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-14
Specifying More Than One Condition
Specifying More Than One Condition
You can use the Boolean operators NOT, AND, and OR to select data that satisfies
more than one condition:
>> SELECT PARTNUM, PARTDESC
+> FROM PARTS
+> WHERE QTY_AVAILABLE < 2500
+> AND PARTNUM BETWEEN 2000 AND 3000
+> OR PARTNUM > 6000 ;
The order of evaluation is:
1. Expressions within parentheses
2. NOT
3. AND
4. OR
For example, if the quantity available is less than 2500, part numbers from 2000
through 3000 are selected. Part numbers greater than 6000 are selected regardless of
the quantity available.
The effect of parentheses is illustrated by this revision of these conditions:
>> SELECT PARTNUM, PARTDESC
+> FROM PARTS
+> WHERE QTY_AVAILABLE < 2500
+> AND ( PARTNUM BETWEEN 2000 AND 3000
+> OR PARTNUM > 6000 ) ;
For example, if the quantity available is less than 2500, part numbers from 2000
through 3000 or greater than 6000 are selected.
If you want NOT to apply to more than one predicate, enclose the predicates in
parentheses:
WHERE NOT (PARTNUM BETWEEN 2000 AND 3000
OR PARTNUM > 6000)
Rows with part numbers between 2000 and 3000 or part numbers greater than 6000
are not selected. All other rows are selected.
Consider these points:
•
The AND that appears in a BETWEEN predicate does not connect two predicates
or search conditions.
•
You can specify NOT BETWEEN, NOT IN, and NOT LIKE, but you cannot specify
NOT =. You must use <> to indicate not equal.
•
You cannot use conditional expressions that require subqueries (EXISTS, ALL,
SOME). Parentheses are not allowed in conditional statements. Use nested IF-
THEN-ELSE statements for these types of queries.