SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-87
Comparison Predicates
This multivalue predicate is equivalent to this condition with three comparison
predicates:
(last_name > 'MOSS') OR
(last_name = 'MOSS' AND first_name > 'DUNCAN')
•
Compare two datetime values START_DATE and the result of the
CURRENT_DATE function:
START_DATE < CURRENT_DATE
•
Compare two datetime values START_DATE and SHIP_TIMESTAMP:
CAST (start_date AS TIMESTAMP) < ship_timestamp
•
Compare two INTERVAL values:
JOB1_TIME < JOB2_TIME
Suppose that JOB1_TIME, defined as INTERVAL DAY TO MINUTE, is 2 days 3
hours, and JOB2_TIME, defined as INTERVAL DAY TO HOUR, is 3 days.
To evaluate the predicate, NonStop SQL/MX converts the two INTERVAL values to
MINUTE. The comparison predicate is true.
•
The next 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
inner query is evaluated when it receives a value from its outer query.
Find all employees whose salary is greater than the maximum salary of employees
in department 1500:
SELECT first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE salary > (SELECT MAX (salary)
FROM persnl.employee
WHERE deptnum = 1500);
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
ROGER GREEN 9000 175500.00
KATHRYN HALL 4000 96000.00
RACHEL MCKAY 4000 118000.00
THOMAS RUDLOFF 2000 138000.40
JANE RAYMOND 3000 136000.00
JERRY HOWARD 1000 137000.10
--- 6 row(s) selected.