NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-55
Examples—Comparison Predicate
For comparisons of INTERVAL values, SQL first converts the intervals to a
common base unit. If no common unit exists, SQL reports an error.
•
If a search condition contains a predicate of the form
expression comparison-operator subquery
and the subquery returns no values, the predicate evaluates to null.
For example, the following 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
•
The following are some simple comparison predicates:
•
The following 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
•
The following 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 the following 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 NonStop SQL/MP Query
Guide.
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.