Comparison of distinct types

You can compare an object with a distinct type only to an object with exactly the same distinct type. You cannot compare data of a distinct type directly to data of its source type. However, you can compare a distinct type to its source type by using a cast function.

The basic rule for comparisons is that the data types of the operands must be compatible. The compatibility rule defines, for example, that all numeric types (SMALLINT, INTEGER, FLOAT, and DECIMAL) are compatible. That is, you can compare an INTEGER value with a value of type FLOAT. However, you cannot compare an object of a distinct type to an object of a different type. You can compare an object with a distinct type only to an object with exactly the same distinct type.

For example, suppose you want to know which products sold more than $100 000.00 in the US in the month of July in 2003 (7/03). Because you cannot compare data of type US_DOLLAR with instances of data of the source type of US_DOLLAR (DECIMAL) directly, you must use a cast function to cast data from DECIMAL to US_DOLLAR or from US_DOLLAR to DECIMAL. Whenever you create a distinct type, Db2 creates two cast functions, one to cast from the source type to the distinct type and the other to cast from the distinct type to the source type. For distinct type US_DOLLAR, Db2 creates a cast function called DECIMAL and a cast function called US_DOLLAR. When you compare an object of type US_DOLLAR to an object of type DECIMAL, you can use one of those cast functions to make the data types identical for the comparison. Suppose table US_SALES is defined like this:

CREATE TABLE US_SALES
  (PRODUCT_ITEM  INTEGER,
   MONTH         INTEGER CHECK (MONTH BETWEEN 1 AND 12),
   YEAR          INTEGER CHECK (YEAR > 1990),
   TOTAL         US_DOLLAR);

Then you can cast DECIMAL data to US_DOLLAR like this:

SELECT PRODUCT_ITEM
  FROM   US_SALES
  WHERE  TOTAL > US_DOLLAR(100000.00)
  AND    MONTH = 7
  AND    YEAR  = 2003;

The casting satisfies the requirement that the compared data types are identical.

You cannot use host variables in statements that you prepare for dynamic execution. As explained in Dynamically executing an SQL statement by using PREPARE and EXECUTE, you can substitute parameter markers for host variables when you prepare a statement, and then use host variables when you execute the statement.

If you use a parameter marker in a predicate of a query, and the column to which you compare the value represented by the parameter marker is of a distinct type, you must cast the parameter marker to the distinct type, or cast the column to its source type.

For example, suppose that distinct type CNUM is defined like this:

CREATE DISTINCT TYPE CNUM AS INTEGER;

Table CUSTOMER is defined like this:

CREATE TABLE CUSTOMER
  (CUST_NUM     CNUM NOT NULL,
   FIRST_NAME   CHAR(30) NOT NULL,
   LAST_NAME    CHAR(30) NOT NULL,
   PHONE_NUM    CHAR(20) WITH DEFAULT,
   PRIMARY KEY  (CUST_NUM));

In an application program, you prepare a SELECT statement that compares the CUST_NUM column to a parameter marker. Because CUST_NUM is of a distinct type, you must cast the distinct type to its source type:

SELECT FIRST_NAME, LAST_NAME, PHONE_NUM FROM CUSTOMER
  WHERE CAST(CUST_NUM AS INTEGER) = ?

Alternatively, you can cast the parameter marker to the distinct type:

SELECT FIRST_NAME, LAST_NAME, PHONE_NUM FROM CUSTOMER
  WHERE CUST_NUM = CAST (? AS CNUM)