In addition to specifying how tables are joined, the WHERE clause specifies conditions for selecting particular rows of data from the result table. The result table is the logical table specified by the FROM clause, select list, and the WHERE clause itself, in the case of joined tables.
Selecting Data for a Report Setting Criteria for Selecting Data
To select only rows describing parts with at least 50 units available, enter:
>> SELECT * +> FROM PARTS
+> WHERE QTY_AVAILABLE >= 50;
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.
>> SELECT S.SUPPNUM, SUPPNAME, P.PARTNUM, +> QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS, +> SAMDBCAT.INVENT.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. (See Specifying More Than One
Condition on page 3-14.)
Comparison predicates and quantified predicates can include these 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.
Selecting Data for a Report 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.)
Selecting Data for a Report Setting Criteria for Selecting Data
These examples illustrate the effect of each predicate when included in the SELECT command:
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE +> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS, +> SAMDBCAT.INVENT.SUPPLIER S +> WHERE P.PARTNUM = PS.PARTNUM +> AND PS.SUPPNUM = S.SUPPNUM
+> ( Substitute predicate example from following text.)
•
This comparison predicate specifies only suppliers identified by numbers less than or equal to 400:+> AND S.SUPPNUM <= 400;
•
Use quantified predicates to select rows based on their relation to all or any rows selected by a separate search condition. For example, you can select PARTSUPP table rows that contain a part cost value greater than the cost of every part in the table supplied by supplier number 6:+> AND PARTCOST > ALL ( SELECT PARTCOST FROM PARTSUPP +> WHERE SUPPNUM = 6 ) ;
The subquery selects the part cost from each row describing parts supplied by supplier number 6. The greatest part cost is $1100.00. The main query selects rows with a part cost value greater than all values selected by the subquery, or rows with a part cost greater than $1100.00.
The next quantified predicate selects rows with a part cost equal to any of the parts supplied by supplier number 1:
+> AND PARTCOST = ANY ( SELECT PARTCOST FROM PARTSUPP +> WHERE SUPPNUM = 1 ) ;
For more examples of quantified predicates, as well as examples of EXISTS predicates, see the SQL/MX Reference Manual.
•
This BETWEEN predicate specifies only suppliers of parts numbered from 4000 through 6103:+> AND P.PARTNUM BETWEEN 4000 AND 6103;
•
Use the IN predicate to locate a specific list of values or all values except those in a specific list. Use this predicate to express multiple conditions efficiently.+> AND P.PARTNUM IN (1, 3, 15);
+> AND P.PARTNUM NOT IN (4, 7, 21, 45);
Selecting Data for a Report Comparing a Set of Columns to a Set of Values
In the next example, the subquery (shown in boldface type) selects from the PARTLOC table part numbers for parts with greater than 500 units on hand in a single location. The main query selects the part description and price for these parts from the PARTS table.
>> SELECT PARTNUM, PARTDESC, PRICE +> FROM SAMDBCAT.SALES.PARTS
+> WHERE PARTNUM IN (SELECT PARTNUM
+> FROM SAMDBCAT.INVENT.PARTLOC +> WHERE QTY_ON_HAND > 500); S> LIST NEXT 2;
PARTNUM PARTDESC PRICE
--- --- --- 2001 GRAPHIC PRINTER,M1 1100.00 2403 DAISY PRINTER,T2 650.00 S>
Note that a subquery select list can contain only one element. The element can be an expression or column name.
To confirm the result of the previous query, you can select the PARTLOC information for the two parts. The QTY_ON_HAND value is greater than 500 in at least one location.
>> SELECT * FROM INVENT.PARTLOC +> WHERE PARTNUM IN ( 2001, 2403 ); S> LIST ALL;
LOC_CODE PARTNUM QTY_ON_HAND --- --- --- A10 2001 800 A88 2403 735 G88 2403 32 P10 2001 0 --- 4 row(s) selected.