SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-61
Examples—Comparison Predicate
To evaluate TIME1 > TIME2, the range of DATETIME fields for each value is
extended to a range that includes all the fields from both values.
If the predicate is evaluated on June 16, the EXTEND functions return these
values, and the comparison predicate returns TRUE:
16:09:06:24 > 15:09:21:00
If the predicate is evaluated on August 15, the EXTEND functions return these
values, and the comparison predicate returns FALSE:
15:09:06:24 > 15:09:21:00

This predicate compares two INTERVAL values:
JOB1_TIME < JOB2_TIME
JOB1_TIME, defined as INTERVAL DAY TO MINUTE, is 2 days 3 hours.
JOB2_TIME, defined as INTERVAL DAY TO HOUR, is 3 days.
To evaluate the predicate, the SQL converts the two INTERVAL values to MINUTE
and finds the comparison predicate to be true.

These examples contain a subquery in a comparison predicate. Each subquery
operates on a separate logical copy of the EMPLOYEE table. The processing
sequence is outer to inner. A row selected by an outer query allows an inner query
to be evaluated and a single value is returned. The next outer query is evaluated
when it receives a value from the inner query.
This query finds all employees whose salary is greater than the maximum salary of
department 1500:
SELECT FIRST_NAME, LAST_NAME
FROM PERSNL.EMPLOYEE
WHERE SALARY > (SELECT MAX (SALARY)
FROM PERSNL.EMPLOYEE
WHERE DEPTNUM = 1500)
In this query, the innermost subquery determines the average salary of employees
in department 1500. Suppose that the default subvolume is PERSNL.
The first outer query of this subquery determines the minimum salary of employees
from other departments whose salary is greater than the average salary for
department 1500. The main query then finds the names of employees who are not
in department 1500 and whose salary is less than the minimum salary determined
by the first outer subquery.
SELECT FIRST_NAME, LAST_NAME
FROM PERSNL.EMPLOYEE
WHERE DEPTNUM <> 1500 AND
SALARY < (SELECT MIN (SALARY) FROM PERSNL.EMPLOYEE
WHERE DEPTNUM <> 1500 AND
SALARY > (SELECT AVG (SALARY) FROM
PERSNL.EMPLOYEE
WHERE DEPTNUM = 1500) )