SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-7
Developing a Query
In the next example, the WHERE clause specifies conditions for selecting rows and
specifies the method for joining three tables. Only information about local suppliers is
selected; that is, suppliers in areas with postal codes between 95400 and 95500.
>> VOLUME INVENT;
>> SELECT S.SUPPNUM, SUPPNAME, P.PARTNUM,
+> QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM
+> AND POSTCODE BETWEEN "95400" AND "95500";
Columns you refer to in the WHERE clause do not have to appear in the select list, but
they must be columns from one of the tables in the FROM clause.
The search condition you specify in a WHERE clause consists of predicates connected
by the Boolean operators NOT, AND, and OR.
Comparison predicates and quantified predicates can include the following comparison
operators:
= Equal
<> Not equal
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
Table 3-1 summarizes the types of predicates you can use to express search
conditions.
Table 3-1. Search Condition Predicates (page 1 of 2)
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.)