User-defined type assignments

User-defined type assignments include distinct type assignments and array assignments.

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.

Example: Assume that distinct type AGE was created with the following SQL statement:

  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

For examples, sssume that the source data type for distinct type AGE is SMALLINT:

  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;

Table 1. Assessment of various assignments for example INSERT statement
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

Array type assignments

An array value can only be assigned to a variable or parameter with a compatible user-defined array type.

The following values can be assigned to an array variable:

The null value.
The value of an array with a user-defined array type, where the source and target arrays have the same user-defined array type. The source value can be an array variable, an invocation of the TRIM_ARRAY function, an invocation of the ARRAY_DELETE function, or a CAST specification. The value of an ordinary array variable or parameter can only be assigned to an ordinary array target. The value of an associative array variable or parameter can only be assigned to an associative array target.
The value of an array without a user-defined array type. The result of an invocation of aggregate built-in function ARRAY_AGG or of an array constructor is an array without an associated user-defined data type.
For an assignment with a FETCH statement, the elements in the source array value must have the same data type as the elements of the user-defined array type of the target array. The index values of the source array value must have the same data type as the index of the user-defined array type of the target array.

For an assignment that is the result of a statement other than FETCH, the source array value is implicitly cast to the target array type.

Assignment of a value to an array element might affect the cardinality of the array, and might result in initializing other new array elements with the null value. Suppose that A is the target array variable, c is the cardinality of array A, idx is an expression that is used as the array index, and SV is the source value. Db2 assigns the values to the elements of the array as follows:

If array A is the null value, A is set to an empty array.
If A is an ordinary array:
- If idx is less than or equal to c, the value in the element of A with array index idx is replaced by the value of SV.
- If idx greater than c:
  - Each element of A with array index i, for every i that is greater than c and less than idx, is set to the null value.
  - The element of A with array index idx is set to the value of SV.
  - The cardinality of A is set to idx.
If A is an associative array:
- If idx matches an existing value of the array index for A, the value of the element with array index idx is replaced by the value of SV.
- If idx does not match an existing value of the array index for A:
  - The element of A with array index idx is set to the value of SV.
  - The cardinality of A is incremented by 1.

The following values can be assigned to an element of an array variable:

The null value
The value of an expression, where the data type of the expression is assignable to the data type of the elements in the target array

Example: Assigning an array to another array

Suppose that arrays PHONELIST and HOMEPHONELIST are defined with the same user-defined array type named PLIST. PLIST is defined with VARCHAR(12) elements. The following statement assigns the values of the HOMEPHONELIST array to the PHONELIST array:

SET PHONELIST = HOMEPHONELIST;

Example: Assigning elements of an array to another array

Suppose that array V is defined with user-defined type MYARRAY. The following statement assigns the values 1, 2, and 3 to array V using an array constructor.

SET V = ARRAY[1,2,3];

This statement is equivalent to the following statement:

SET V = CAST(ARRAY[1,2,3] AS MYARRAY);

Example: Assigning values from a column to an array

Suppose that array V is defined with user-defined type MYARRAY. The following statement assigns the values from the column C1 in table T to array V using the ARRAY_AGG function.

SELECT ARRAY_AGG(C1) INTO V FROM T;

This statement is equivalent to the following statement:

SELECT CAST(ARRAY_AGG(C1) AS MYARRAY) INTO V FROM T;