Distinct type comparisons

A value with a distinct type can only be compared to another value with exactly the same type because distinct types have strong typing, which means that a distinct type is compatible only with its own type.

To compare a distinct type to a value with a different data type, the distinct type value must be cast to the data type of the comparison value or the comparison value must be cast to the distinct type. For example, because constants are built-in data types, a constant can be compared to a distinct type value only if it is first cast to the distinct type or vice versa.

The following table shows examples of valid and invalid comparisons, assuming the following SQL statements were used to define two distinct types AGE_TYPE and CAMP_DATE and table CAMP_ROSTER table. 
  CREATE TYPE AGE_TYPE AS INTEGER;
  CREATE TYPE CAMP_DATE AS DATE;
  CREATE TABLE CAMP_ROSTER
     ( NAME                VARCHAR(20),
       ATTENDEE_NUMBER     INTEGER NOT NULL,
       AGE                 AGE_TYPE,
       FIRST_CAMP_DATE     CAMP_DATE,
       LAST_CAMP_DATE      CAMP_DATE,
       BIRTHDATE           DATE);
Table 1. Examples of valid and invalid comparisons involving distinct types
SQL statement Valid Reason
Distinct types with distinct types 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE < LAST_CAMP_DATE;
 Yes Both values are the same distinct type.
Distinct types with columns of the same source data type 
SELECT * FROM CAMP_ROSTER
   WHERE AGE > ATTENDEE_NUMBER;
 No A distinct type cannot be compared to integer. 
SELECT * FROM CAMP_ROSTER
   WHERE INTEGER(AGE) > ATTENDEE_NUMBER;
SELECT * FROM CAMP_ROSTER
   WHERE CAST(AGE AS INTEGER) > ATTENDEE_NUMBER;
 Yes The distinct type is cast to an integer, making the comparison of two integers. 
SELECT * FROM CAMP_ROSTER
   WHERE AGE > AGE_TYPE(ATTENDEE_NUMBER);
SELECT * FROM CAMP_ROSTER
   WHERE AGE > CAST(ATTENDEE_NUMBER as AGE_TYPE);
 Yes Integer ATTENDEE_NUMBER is cast to the distinct type AGE_TYPE, making both values the same distinct type.
Distinct types with constants 
SELECT * FROM CAMP_ROSTER
   WHERE AGE IN (15,16,17);
 No A distinct type cannot be compared to a constant. 
SELECT * FROM CAMP_ROSTER
   WHERE INTEGER(AGE) IN (15,16,17);
 Yes The distinct type is cast to the data type of constants, making all the values in the comparison integers. 
SELECT * FROM CAMP_ROSTER
   WHERE AGE IN
   (AGE_TYPE(15),AGE_TYPE(16),AGE_TYPE(17));
 Yes Constants are cast to distinct type AGE_TYPE, making all the values in the comparison the same distinct type. 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE > '06/12/99';
 No A distinct type cannot be compared to a constant. 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE >
   CAST('06/12/99' AS CAMP_DATE);
 No The string constant '06/12/99', a VARCHAR data type, cannot be cast directly to distinct type CAMP_DATE, which is based on a DATE data type. As illustrated in the next row, the constant must be cast to a DATE data type and then to the distinct type. 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE >
   CAST(DATE('06/12/1999') AS CAMP_DATE);
Yes The string constant '06/12/99' is cast to the distinct type CAMP_DATE, making both values the same distinct type. To cast a string constant to a distinct type that is based on a DATE, TIME, or TIMESTAMP data type, the string constant must first be cast to a DATE, TIME, or TIMESTAMP data type.
Distinct types with host variables 
SELECT * FROM CAMP_ROSTER
   WHERE AGE BETWEEN :HV_INTEGER AND :HV_INTEGER2;
 No The host variables have integer data types. A distinct type cannot be compared to an integer. 
SELECT * FROM CAMP_ROSTER
   WHERE AGE
   BETWEEN CAST(:HV_INTEGER AS AGE_TYPE)
   AND AGE_TYPE(:HV_INTEGER2);
 Yes The host variables are cast to distinct type AGE_TYPE, making all the values the same distinct type. 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE > :HV_VARCHAR;
 No The host variable has a VARCHAR data type. A distinct type cannot be compared to a VARCHAR. 
SELECT * FROM CAMP_ROSTER
   WHERE FIRST_CAMP_DATE >
   CAST(DATE(:HV_VARCHAR) AS CAMP_DATE);
 Yes The host variable is cast to the distinct type CAMP_DATE, making both values the same distinct type. To cast a VARCHAR host variable to a distinct type that is based on a DATE, TIME, or TIMESTAMP data type, the host variable must first be cast to a DATE, TIME, or TIMESTAMP data type.