SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-41
Using Multivalued Comparison Predicates in
Context-Free Servers
Note that there are two conditions for the name Smith, so that the query retrieves
information about other employees with the last name of Smith as well as employees
whose last name follows Smith in the alphabet.
You can reformulate the preceding query using a multivalued comparison predicate, as
follows:
SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME, FIRST_NAME > "Smith", "James" ;
Both formulations return this result:
EMP_ID LAST_NAME FIRST_NAME
------ --------- ----------
5361 Smythe Roger
9069 Smith John
9502 Smithson Richard
--- 3 row(s) selected.
The second formulation can result in a more efficient plan. For more information about
efficiency, see Writing Efficient Predicates on page 3-15.
Using Multivalued Comparison Predicates in Context-Free
Servers
Multivalued comparison predicates can be useful in context-free servers. For example,
suppose that a server processes a batch of employees for each request from the
requester. The server then positions to a row following the one that has a key value
supplied by the requester.
A multivalued predicate can be matched with a group of key columns for use as a
begin or end key predicate only if all of the key columns have the same ordering
attribute: either all must be ascending, or all must be descending, as defined in the
KEY specifications of CREATE TABLE and the CREATE INDEX statements.
Aggregate Functions in Predicates
Aggregate functions compute a value. They take a set of rows as their arguments and
return a single row as their result.
Table 1-5. Aggregate Functions
Function Description
AVG Computes the average of a set of numbers
MAX Determines a maximum value
MIN Determines a minimum value
SUM Computes the sum of a set of numbers
COUNT Counts the number of rows that result from the query