The rules that apply to the assignments of distinct types to variables are different than the rules for all other assignments that involve distinct types.
The assignment of a distinct type to a variable is based on the source data type of the distinct type. Therefore, the value of a distinct type is assignable to a variable only if the source data type of the distinct type is assignable to the variable.
Example: Assume that distinct type AGE was created with the following SQL statement and column STU_AGE in table STUDENTS was defined with that distinct type. Using the CL_SCHED table, select all the classes (CLASS_CODE) that start (STARTING) later today. Today's classes have a value of 3 in the DAY column.
CREATE DISTINCT TYPE AGE AS SMALLINT WITH COMPARISONS
When the statement is executed, the following cast functions are also generated:
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 the 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 sourced 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.
A distinct type can be either 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 variable when:
Any value can be assigned to a distinct type when:
Example: Assume that the source data type for distinct type AGE is SMALLINT:
CREATE DISTINCT TYPE AGE AS SMALLINT WITH COMPARISONS
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, Table 16 shows whether the assignments are valid.
INSERT INTO TABLE1 (Y) SELECT X FROM TABLE2
TABLE2.X | TABLE1.Y | Valid | Reason |
---|---|---|---|
AGECOL | AGECOL | Yes | Source and target are same distinct type |
SMINTCOL | AGECOL | Yes | SMALLINT can be cast to AGE (because AGE's source type is SMALLINT) |
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 SMALLINT |
AGECOL | INTCOL | No | AGE cannot be cast to INTEGER |
AGECOL | DECCOL | No | AGE cannot be cast to DECIMAL |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.