Values with a user-defined type can only be compared with values of exactly the same user-defined type. The user-defined type must have been defined using the WITH COMPARISONS clause.
Example:
Given the following YOUTH distinct type and CAMP_DB2_ROSTER table:
CREATE DISTINCT TYPE YOUTH AS INTEGER WITH COMPARISONS
CREATE TABLE CAMP_DB2_ROSTER
( NAME VARCHAR(20),
ATTENDEE_NUMBER INTEGER NOT NULL,
AGE YOUTH,
HIGH_SCHOOL_LEVEL YOUTH)
The following comparison is valid:
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > HIGH_SCHOOL_LEVEL
The following comparison is not valid:
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > ATTENDEE_NUMBER
However, AGE can be compared to ATTENDEE_NUMBER by using a function or CAST specification to cast between the distinct type and the source type. The following comparisons are all valid:
SELECT * FROM CAMP_DB2_ROSTER
WHERE INTEGER(AGE) > ATTENDEE_NUMBER
SELECT * FROM CAMP_DB2_ROSTER
WHERE CAST( AGE AS INTEGER) > ATTENDEE_NUMBER
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > YOUTH(ATTENDEE_NUMBER)
SELECT * FROM CAMP_DB2_ROSTER
WHERE AGE > CAST(ATTENDEE_NUMBER AS YOUTH)