Inserting into UDT Columns - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Inserting into Distinct UDT Columns

To insert into a distinct column, either of the following must be true:
  • The inserted value must be of the same distinct type as the column.
  • There must exist a cast, either system-generated or user-defined, that converts the type of the inserted value to the distinct type of the column, and that cast was created with the AS ASSIGNMENT option. See "CREATE CAST" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
A distinct value can be constructed from any of the following:
  • A cast expression where the target is the same distinct type.
  • An instance method invocation that returns the same distinct type.
  • A UDF invocation that returns the same distinct type.
  • A column reference.
  • A null.

For example, suppose you have the following two table definitions:

     CREATE TABLE table_1 (
       column1 euro,
       column2 INTEGER)
     UNIQUE PRIMARY INDEX(column2);
     CREATE TABLE table_2 (
       column1 euro,
       column2 INTEGER)
     UNIQUE PRIMARY INDEX(column2);

For this case, the information in the following table is true:

Example Comment 
INSERT INTO table_1
VALUES (5.64, 1);
Valid if there is a cast with AS ASSIGNMENT, whether system-generated or user-defined, to convert a DECIMAL type to the distinct type euro. 
INSERT INTO table_1
VALUES (CAST(5.64 AS euro), 1);
Valid because an explicit cast is specified for conversion from the source type euro. 
INSERT INTO table_1
SELECT CAST(us_dollar_col AS euro), c2
FROM us_sales;
Valid if the cast from us_dollar to euro is valid. 
INSERT INTO table_1 						
SELECT column1.roundup(0), column2
FROM TAB2;
Valid if the method roundup() returns the euro type. 
INSERT INTO table_1 						
SELECT column1, column2 from table_2;
Valid column reference. 
INSERT INTO table_1
VALUES (NULL, 8);
 Valid because the request insert a null into a distinct type column.

Inserting into Structured UDT Columns

To insert into a structured type column, either of the following must be true:
  • The inserted value must be of the same structured type.
  • There must exist a cast that converts the type of the inserted value to the structured type of the column, and the cast was created with the AS ASSIGNMENT option. See the CREATE CAST statement in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
A structured type can be constructed from any of the following:
  • A NEW specification (constructor method invocation).
  • A constructor function invocation.
  • A UDF invocation that returns the same structured type.
  • Mutator method invocations.
  • An instance method invocation that returns the same structured type.
  • A cast expression where the target data type is the same structured type.
  • A column reference.
  • A null.

For example, suppose you have the following two table definitions:

     CREATE TABLE table_1 (
       column1 address,
       column2 INTEGER)
     UNIQUE PRIMARY INDEX(column2);
     CREATE TABLE table_2 (
       column1 address,
       column2 INTEGER)
     UNIQUE PRIMARY INDEX(column2);

For this case, the information in the following table is true:

Example Comment 
INSERT INTO table_1
VALUES ('17095 Via Del Campo;92127', 1 );
 Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address. 
USING (addr VARCHAR(30), c2 INTEGER)
INSERT INTO table_1 (:addr, :c2 );
Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address. 
INSERT INTO table_1
VALUES (NEW address('17095 Via Del Campo', '92127'), 1 );
Valid invocation of a constructor method. 
USING (street varchar(20), zip char(5))
INSERT INTO TAB1
VALUES (NEW address(:street, :zip), 2 );
Valid invocation of a constructor method with host variables. 
INSERT INTO TAB1
VALUES ( NEW address(), 3 );
Valid invocation of a constructor function. 
INSERT INTO TAB1
VALUES ( NEW address().street('17095 Via Del Campo').zip('92127'), 4 );
Valid invocations of mutator methods:
  1. The constructor function is invoked.

    The result is an address value whose attribute values are set to their defaults.

  2. The mutator for the street attribute is invoked.

    The result is an updated address value with its street attribute modified.

  3. The mutator for the zip attribute is invoked.

    The result is another updated address value with its zip attribute modified. This result also contains the previous change to the street attribute.

  4. The result address value is inserted into column1 of table_1.
 
INSERT INTO table_1
  SELECT empl.residence(), table_2.column2
  FROM table_2, table_3;
Valid if method empl.residence() returns the address data type. 
INSERT INTO table_1
  VALUES (SELECT CAST(intl_addr_col AS           address), c2)
FROM table_x;
Valid if the cast from intl_addr to the structured type address is valid. 
INSERT INTO table_1
SELECT column1, column2
FROM table_2;
Valid column reference. 
INSERT INTO table_1
VALUES (NULL, 8);
 Valid insertion of a null into a structured type column.