Inserting Values into ROW-Type Columns (IDS)

The VALUES clause to insert literal and nonliteral values in a named or unnamed ROW type column, as in the following example:

CREATE ROW TYPE address_t 
(
   street CHAR(20), 
   city CHAR(15),
   state CHAR(2),
   zipcode CHAR(9)
);
CREATE TABLE employee 
(
   name ROW ( fname CHAR(20), lname CHAR(20)),
   address address_t
);

The next example inserts literal values in the name and address columns:

INSERT INTO employee VALUES 
   (
      ROW('John', 'Williams'),
      ROW('103 Baker St', 'Tracy','CA', 94060)::address_t
   )

INSERT uses ROW constructors to generate values for the name column (an unnamed ROW data type) and the address column (a named ROW data type). When you specify a value for a named ROW data type, you must use the CAST AS keywords or the double colon ( :: ) operator, with the name of the ROW data type, to cast the value to the named ROW data type.

For the syntax of ROW constructors, see Constructor Expressions (IDS) in the Expression segment. For information on literal values for named ROW and unnamed ROW data types, see Literal Row.

When you use a ROW variable in the VALUES clause, the ROW variable must contain values for each field value. For more information, see Inserting into a Row Variable (ESQL/C, IDS, SPL).

You can use ESQL/C host variables to insert nonliteral values in two ways:

• An entire ROW type into a column. Use a row variable in the VALUES clause to insert values for all fields in a ROW column at one time.
• Individual fields of a ROW type. To insert nonliteral values in a ROW-type column, insert the elements into a row variable and then specify the collection variable in the SET clause of an UPDATE statement.