Distinct type assignments
The rules that apply to the assignments of distinct types to host variables are different than the rules for all other assignments that involve distinct types.
Assignments to host variables: The assignment of distinct type to a host variable is based on the source data type of the distinct type. Therefore, the value of a distinct type is assignable to a host variable only if the source data type of the distinct type is assignable to the host variable.
CREATE TYPE AGE AS SMALLINT;
When
the statement was executed, DB2® also
generated these cast functions: AGE (SMALLINT) RETURNS AGE
AGE (INTEGER) RETURNS AGE
SMALLINT (AGE) RETURNS SMALLINT
Next, assume that column
STU_AGE was defined in table STUDENTS with distinct type AGE. Now,
consider this valid assignment of a student's age to host variable HV_AGE,
which has an INTEGER data type: SELECT STU_AGE INTO :HV_AGE FROM STUDENTS WHERE STU_NUMBER = 200;
The distinct type value is assignable to host variable HV_AGE because the source data type of the distinct type (SMALLINT) is assignable to the host variable (INTEGER). If distinct type AGE had been based on a character data type such as CHAR(5), the above assignment would be invalid because a character type cannot be assigned to an integer type.
Assignments other than to host variables: A distinct type can be the source or target of an assignment. Assignment is based on whether the data type of the value to be assigned is castable to the data type of the target. (Casting between data types shows which casts are supported when a distinct type is involved). Therefore, a distinct type value can be assigned to any target other than a host variable when:
- The target of the assignment has the same distinct type, or
- The distinct type is castable to the data type of the target
Any value can be assigned to a distinct type when:
- The value to be assigned has the same distinct type as the target, or
- The data type of the assigned value is castable to the target distinct type
CREATE TYPE AGE AS SMALLINT;
Next,
assume that two tables TABLE1 and TABLE2 were created with four identical
column descriptions: AGECOL AGE
SMINTCOL SMALLINT
INTCOL INTEGER
DECCOL DEC(6,2)
Using the following SQL statement and
substituting various values for X and Y to insert values into various
columns of TABLE1 from TABLE2, the following table shows whether the
assignments are valid. DB2 uses
assignment rules in this INSERT statement to determine if X can be
assigned to Y. INSERT INTO TABLE1 (Y)
SELECT X FROM TABLE2;
X (column
in TABLE2) |
Y (column
in TABLE1) |
Valid | Reason |
---|---|---|---|
AGECOL | AGECOL | Yes | Source and target are same distinct type |
SMINTCOL | AGECOL | Yes | SMALLINT can be cast to AGE |
INTCOL | AGECOL | Yes | INTEGER can be cast to AGE (because AGE's source type is SMALLINT) |
DECCOL | AGECOL | No | DECIMAL cannot be cast to AGE |
AGECOL | SMINTCOL | Yes | AGE can be cast to its source type of SMALLINT |
AGECOL | INTCOL | No | AGE cannot be cast to INTEGER |
AGECOL | DECCOL | No | AGE cannot be cast to DECIMAL |