Distinct type comparisons

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 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