SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-60
Examples—Comparison Predicate
expression comparison-operator subquery
and the subquery returns no values, the predicate evaluates to null.
For example, this predicate evaluates to null because the subquery returns no
value (there is no part number with more than 1500 units in stock):
PARTNUM = (SELECT PARTNUM
FROM ODETAIL
WHERE QTY_ORDERED > 1500)
Examples—Comparison Predicate

These are some simple comparison predicates:

This comparison predicate evaluates to null for any rows in either CUSTOMER or
ORDERS that contain a null value in the CUSTNUM column:
CUSTOMER.CUSTNUM > ORDERS.CUSTNUM

This example uses a multivalue comparison predicate to compare multiple values.
Use multivalue predicates whenever possible; they are generally more efficient
than equivalent search conditions without multivalue predicates.
In this example, the multivalue predicate returns information about anyone whose
name follows MOSS, DUNCAN in a list arranged alphabetically by last name and,
for the same last name, alphabetically by first name. REEVES, ANNE meets this
criteria, but MOSS, ANNE does not.
LAST_NAME, FIRST_NAME >= "MOSS" , "DUNCAN"
The multivalue predicate is equivalent to this search condition relating three
comparison predicates:
( LAST_NAME > "MOSS" ) OR
( LAST_NAME = "MOSS" AND FIRST_NAME >= "DUNCAN" )
For guidelines about using multivalued predicates, see the SQL/MP Query Guide.

This comparison predicate compares two DATETIME values:
EXTEND (TIME1, DAY TO SECOND)
> EXTEND (TIME2, DAY TO SECOND)
TIME1, defined as DATETIME HOUR TO SECOND, is 09:06:24.
TIME2, defined as DATETIME DAY TO MINUTE, is 15:09:21.
CUSTNUM = 3210 The customer number is equal to
3210.
SALARY > (SELECT AVG (SALARY)
FROM PERSNL.EMPLOYEE)
The salary is greater than the average
salary of all employees.
CUSTNAME = "BACIGALUPI" The customer name is BACIGALUPI.