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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-86
Comparison Predicates
For example, NonStop SQL/MX considers the string ‘JOE’ equal to a value JOE stored
in a column of data type CHAR or VARCHAR of width three or more. Similarly,
NonStop SQL/MX considers a value JOE stored in any column of the CHAR data type
equal to the value JOE stored in any column of the VARCHAR data type.
Two strings are equal if all characters in the same ordinal position are equal.
Lowercase and uppercase letters are not considered equivalent.
Comparing Numeric Data
Before evaluation, all numeric values in an expression are first converted to the
maximum precision needed anywhere in the expression.
Comparing Interval Data
For comparisons of INTERVAL values, NonStop SQL/MX first converts the intervals to
a common unit. If no common unit exists, NonStop SQL/MX reports an error. Two
INTERVAL values must be both year-month intervals or both day-time intervals.
Comparing Multiple Values
Use multivalue predicates whenever possible; they are generally more efficient than
equivalent conditions without multivalue predicates.
Examples of Comparison Predicates
•
This predicate is true if the customer number is equal to 3210:
custnum = 3210
•
This predicate is true if the salary is greater than the average salary of all
employees:
salary >
(SELECT AVG (salary) FROM persnl.employee);
•
This predicate is true if the customer name is BACIGALUPI:
custname = 'BACIGALUPI'
•
This predicate evaluates to unknown for any rows in either CUSTOMER or
ORDERS that contain null in the CUSTNUM column:
customer.custnum > orders.custnum
•
This 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:
(last_name, first_name) > ('MOSS', 'DUNCAN')
REEVES, ANNE meets this criteria, but MOSS, ANNE does not.