Cast parameter markers to distinct types or distinct types to source types

When you use a distinct-type parameter in the predicate of a query statement, you must use a CAST function. You can cast either the parameter marker to a distinct type, or you can cast the distinct type to a source type.

Example: Assume that you define the following distinct type and table:

CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS

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

Then you issue the following query statement:

SELECT  first_name, last_name, phone_num FROM customer
     where cust_num = ?

This query fails because the comparison includes incompatible types; the parameter marker cannot be type CNUM.

To successfully execute the statement, issue a query that casts the parameter marker to the distinct type CNUM:

SELECT  first_name, last_name, phone_num FROM customer
       where cust_num = cast( ? as cnum )

Alternatively, issue a query that casts the data type of the column to the source type INTEGER:

SELECT  first_name, last_name, phone_num FROM customer
       where cast( cust_num as integer ) = ?