DB2 10.5 for Linux, UNIX, and Windows

Field reference

A field of a row type is referenced using the field name qualified by a variable that returns a row type which includes a field with that field name, or an array element specification that returns a row type which includes a field with that field name.


field-reference

|--+-row-variable-name---------------+--.field-name-------------|
   '-row-array-element-specification-'

row-variable-name: The name of a variable with a data type that is a row type.
row-array-element-specification: An array-element-specification where the data type of the array element is a row type.
field-name: The name of a field within the row type.

Examples

To reference a field of a simple row type:

CREATE OR REPLACE  TYPE REC_TYPE AS ROW
(
   ID INTEGER,
   NAME VARCHAR(10)
)@

BEGIN
     DECLARE NAME VARCHAR(10);
     DECLARE TMPREC REC_TYPE;
     SET NAME = TMPREC.NAME;
END @

To reference a field of a nested row type, you must assign the inner row to a temporary variable before a field of this inner row can be referenced:

CREATE OR REPLACE TYPE INNER_REC_TYPE AS ROW
(
  ID INTEGER,
  NAME VARCHAR(10)
)@
CREATE OR REPLACE  TYPE REC_TYPE AS ROW
(
  INNER_REC INNER_REC_TYPE
)@
CREATE OR REPLACE TYPE ARRAY_TYPE AS REC_TYPE ARRAY[INTEGER]@

BEGIN
  DECLARE TMPRECORD INNER_REC_TYPE;
  DECLARE TMPARRAY ARRAY_TYPE;
  DECLARE NAME VARCHAR(10);
 
  SET TMPRECORD = TMPARRAY[1].INNER_REC;
  SET NAME = TMPRECORD.NAME;
  END @