SQL Reference

User-defined Type Comparisons

Values with a user-defined distinct type can only be compared with values of exactly the same user-defined distinct type. The user-defined distinct 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)

Values with a user-defined structured type cannot be compared with any other value (the NULL predicate and the TYPE predicate can be used).