SQL Reference

CREATE DISTINCT TYPE

The CREATE DISTINCT TYPE statement defines a distinct type. The distinct type is always sourced on one of the built-in data types. Successful execution of the statement also generates functions to cast between the distinct type and its source type and, optionally, generates support for the comparison operators (=, <>, <, <=, >, and >=) for use with the distinct type.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include as least one of the following:

SYSADM or DBADM authority
IMPLICIT_SCHEMA authority on the database, if the schema name of the distinct type does not refer to an existing schema.
CREATEIN privilege on the schema, if the schema name of the distinct type refers to an existing schema.

Syntax

>>-CREATE DISTINCT TYPE--distinct-type-name--AS----------------->
 
                                             (1)
>-----| source-data-type |--WITH COMPARISONS-------------------><
 
source-data-type
 
|---+-SMALLINT-------------------------------------------------+->
    +-+-INTEGER-+----------------------------------------------+
    | '-INT-----'                                              |
    +-BIGINT---------------------------------------------------+
    +-+-FLOAT--+----------------+-+----------------------------+
    | |        '-(--integer--)--' |                            |
    | +-REAL----------------------+                            |
    | |        .-PRECISION-.      |                            |
    | '-DOUBLE-+-----------+------'                            |
    +--+-DECIMAL-+---+--------------------------------+--------+
    |  +-DEC-----+   '-(--integer--+-----------+---)--'        |
    |  +-NUMERIC-+                 '-,integer--'               |
    |  '-NUM-----'                                             |
    +--+--+-CHARACTER-+---+------------+-+---+--------------+--+
    |  |  '-CHAR------'   '-(integer)--' |   '-FOR BIT DATA-'  |
    |  +-VARCHAR(integer)----------------+                     |
    |  '-LONG VARCHAR--------------------'                     |
    +-GRAPHIC--+------------+----------------------------------+
    |          '-(integer)--'                                  |
    +-VARGRAPHIC(integer)--------------------------------------+
    +-LONG VARGRAPHIC------------------------------------------+
    +-DATE-----------------------------------------------------+
    +-TIME-----------------------------------------------------+
    +-TIMESTAMP------------------------------------------------+
    +--+-BLOB---+--(--integer--+---+---)-----------------------+
    |  +-CLOB---+              +-K-+                           |
    |  '-DBCLOB-'              +-M-+                           |
    |                          '-G-'                           |
    '-DATALINK--+------------+---------------------------------'
                '-(integer)--'
 
>---------------------------------------------------------------|

Notes:

Required for all source-data-types except LOBs, LONG VARCHAR, LONG VARGRAPHIC and DATALINK which are not supported.

Description

distinct-type-name

Names the distinct type. The name, including the implicit or explicit qualifier must not identify a distinct type described in the catalog. The unqualified name must not be the same as the name of a source-data-type or BOOLEAN (SQLSTATE 42918).

In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

The schema name (implicit or explicit) must not be greater than 8 bytes (SQLSTATE 42622).

A number of names used as keywords in predicates are reserved for system use, and may not be used as a distinct-type-name. The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators as described in Basic Predicate. Failure to observe this rule will lead to an error (SQLSTATE 42939).

If a two-part distinct-type-name is specified, the schema name cannot begin with "SYS"; otherwise, an error (SQLSTATE 42939) is raised.

source-data-type

Specifies the data type used as the basis for the internal representation of the distinct type. For information about the association of distinct types with other data types, see Distinct Types. For information about data types, see CREATE TABLE.

WITH COMPARISONS

Specifies that system-generated comparison operators are to be created for comparing two instances of a distinct type. These keywords should not be specified if the source-data-type is BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC, or DATALINK, otherwise a warning will be returned (SQLSTATE 01596) and the comparison operators will not be generated. For all other source-data-types, the WITH COMPARISONS keywords are required.

Notes

Creating a distinct type with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
The following functions are generated to cast to and from the source type:
- One function to convert from the distinct type to the source type
- One function to convert from the source type to the distinct type
- One function to convert from INTEGER to the distinct type if the source type is SMALLINT
- one function to convert from VARCHAR to the distinct type if the source type is CHAR
- one function to convert from VARGRAPHIC to the distinct type if the source type is GRAPHIC.

In general these functions will have the following format:

   CREATE FUNCTION source-type-name (distinct-type-name)
      RETURNS source-type-name ...
 
   CREATE FUNCTION distinct-type-name (source-type-name)
      RETURNS distinct-type-name ...

In cases in which the source type is a parameterized type, the function to convert from the distinct type to the source type will have as function name the name of the source type without the parameters (see Table 20 for details). The type of the return value of this function will include the parameters given on the CREATE DISTINCT TYPE statement. The function to convert from the source type to the distinct type will have an input parameter whose type is the source type including its parameters. For example,

   CREATE DISTINCT TYPE T_SHOESIZE AS CHAR(2) 
              WITH COMPARISONS
 
   CREATE DISTINCT TYPE T_MILES AS DOUBLE 
              WITH COMPARISONS

will generate the following functions:

   FUNCTION CHAR (T_SHOESIZE) RETURNS CHAR (2)
 
   FUNCTION T_SHOESIZE (CHAR (2)) 
   RETURNS T_SHOESIZE
 
   FUNCTION DOUBLE (T_MILES) RETURNS DOUBLE
 
   FUNCTION T_MILES (DOUBLE) RETURNS T_MILES

The schema of the generated cast functions is the same as the schema of the distinct type. No other function with this name and with the same signature may already exist in the database (SQLSTATE 42710).

The following table gives the names of the functions to convert from the distinct type to the source type and from the source type to the distinct type for all predefined data types.

Table 20. CAST functions on distinct types

Source Type Name Function Name Parameter Return-type
CHAR <distinct> CHAR (n) <distinct>
CHAR <distinct> CHAR (n)
<distinct> VARCHAR (n) <distinct>
VARCHAR <distinct> VARCHAR (n) <distinct>
VARCHAR <distinct> VARCHAR (n)
LONG VARCHAR <distinct> LONG VARCHAR <distinct>
LONG_VARCHAR <distinct> LONG VARCHAR
CLOB <distinct> CLOB (n) <distinct>
CLOB <distinct> CLOB (n)
BLOB <distinct> BLOB (n) <distinct>
BLOB <distinct> BLOB (n)
GRAPHIC <distinct> GRAPHIC (n) <distinct>
GRAPHIC <distinct> GRAPHIC (n)
<distinct> VARGRAPHIC (n) <distinct>
VARGRAPHIC <distinct> VARGRAPHIC (n) <distinct>
VARGRAPHIC <distinct> VARGRAPHIC (n)
LONG VARGRAPHIC <distinct> LONG VARGRAPHIC <distinct>
LONG_VARGRAPHIC <distinct> LONG VARGRAPHIC
DBCLOB <distinct> DBCLOB (n) <distinct>
DBCLOB <distinct> DBCLOB (n)
SMALLINT <distinct> SMALLINT <distinct>
<distinct> INTEGER <distinct>
SMALLINT <distinct> SMALLINT
INTEGER <distinct> INTEGER <distinct>
INTEGER <distinct> INTEGER
BIGINT <distinct> BIGINT <distinct>
BIGINT <distinct> BIGINT
DECIMAL <distinct> DECIMAL (p,s) <distinct>
DECIMAL <distinct> DECIMAL (p,s)
NUMERIC <distinct> DECIMAL (p,s) <distinct>
DECIMAL <distinct> DECIMAL (p,s)
REAL <distinct> REAL <distinct>
<distinct> DOUBLE <distinct>
REAL <distinct> REAL
FLOAT(n) where n<=24 <distinct> REAL <distinct>
<distinct> DOUBLE <distinct>
REAL <distinct> REAL
FLOAT(n) where n>24 <distinct> DOUBLE <distinct>
DOUBLE <distinct> DOUBLE
FLOAT <distinct> DOUBLE <distinct>
DOUBLE <distinct> DOUBLE
DOUBLE <distinct> DOUBLE <distinct>
DOUBLE <distinct> DOUBLE
DOUBLE PRECISION <distinct> DOUBLE <distinct>
DOUBLE <distinct> DOUBLE
DATE <distinct> DATE <distinct>
DATE <distinct> DATE
TIME <distinct> TIME <distinct>
TIME <distinct> TIME
TIMESTAMP <distinct> TIMESTAMP <distinct>
TIMESTAMP <distinct> TIMESTAMP
DATALINK <distinct> DATALINK <distinct>
DATALINK <distinct> DATALINK

Note: NUMERIC and FLOAT are not recommended when creating a user-defined type for a portable application. DECIMAL and DOUBLE should be used instead.

The functions described in the above table are the only functions that are generated automatically when distinct types are defined. Consequently, none of the built-in functions (AVG, MAX, LENGTH, etc.) are supported on distinct types until the CREATE FUNCTION statement (see CREATE FUNCTION) is used to register user-defined functions for the distinct type, where those user-defined functions are sourced on the appropriate built-in functions. In particular, note that it is possible to register user-defined functions that are sourced on the built-in column functions.

When a distinct type is created using the WITH COMPARISONS clause, system-generated comparison operators are created. Creation of these comparison operators will generate entries in the SYSCAT.FUNCTIONS catalog view for the new functions.

The schema name of the distinct type must be included in the SQL path (see SET PATH or the FUNCPATH BIND option as described in the Application Development Guide) for successful use of these operators and cast functions in SQL statements.

Examples

Example 1: Create a distinct type named SHOESIZE that is based on an INTEGER data type.

   CREATE DISTINCT TYPE SHOESIZE AS INTEGER WITH COMPARISONS

This will also result in the creation of comparison operators (=, <>, <, <=, >, >=) and cast functions INTEGER(SHOESIZE) returning INTEGER and SHOESIZE(INTEGER) returning SHOESIZE.

Example 2: Create a distinct type named MILES that is based on a DOUBLE data type.

   CREATE DISTINCT TYPE MILES AS DOUBLE WITH COMPARISONS

This will also result in the creation of comparison operators (=, <>, <, =, >, >=) and cast functions DOUBLE(MILES) returning DOUBLE and MILES(DOUBLE) returning MILES.

[ Top of Page | Previous Page | Next Page ]