A value with a distinct type can be compared only to another value with exactly the same distinct type.
For example, assume that distinct type YOUTH and table CAMP_DB2_ROSTER table were created with the following SQL statements:
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 because AGE and HIGH_SCHOOL_LEVEL have the same distinct type:
SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > HIGH_SCHOOL_LEVEL
The following comparison is not valid:
SELECT * FROM CAMP_DB2_ROSTER ***INCORRECT*** WHERE AGE > ATTENDEE_NUMBER
However, AGE can be compared to ATTENDEE_NUMBER by using a cast function or CAST specification to cast between the distinct type and the source type. All of the following comparisons are valid:
SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > YOUTH(ATTENDEE_NUMBER) SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > CAST( ATTENDEE_NUMBER AS YOUTH) SELECT * FROM CAMP_DB2_ROSTER WHERE INTEGER(AGE) > ATTENDEE_NUMBER SELECT * FROM CAMP_DB2_ROSTER WHERE CAST(AGE AS INTEGER) > ATTENDEE_NUMBER
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.