NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-56
Examples—Comparison Predicate
•
The following 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.
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 the following
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 the
following values, and the comparison predicate returns FALSE:
15:09:06:24 > 15:09:21:00
•
The following 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.
•
The following 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.
The following 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 the following query, the innermost subquery determines the average salary of
employees in department 1500. Suppose 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