Distinct type comparisons

The rules for comparisons involving user-defined type values depend on whether the distinct type is a strongly typed distinct type or a weakly typed distinct type.

Strongly typed distinct type comparisons

A value with a strongly typed distinct type can be compared only to another value with exactly the same strongly typed 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

   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

Weakly typed distinct type comparisons

Values with a weakly typed distinct type are compared according to the comparison rules for the source type of the distinct type. It is valid to compare different weakly typed distinct types if the underlying source types are comparable.