16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Consider the following statement that creates a structured UDT named address :

CREATE TYPE address
AS (street VARCHAR(20)
   ,zip CHAR(5))
NOT FINAL;

The following statement creates a table that defines an address column named location :

CREATE TABLE european_sales
  (region INTEGER
  ,location address
  ,sales DECIMAL(8,2));

The following statement uses NEW to insert an address value into the european_sales table:

INSERT european_sales (1001, NEW address(), 0);

Teradata Database selects the default constructor function that was automatically generated for the address UDT because the argument list is empty and the address UDT was created with no constructor method. The default address constructor function initializes the street and zip attributes to NULL.

The following statement is equivalent to the preceding INSERT statement but calls the constructor function instead of using NEW:

INSERT european_sales (1001, address(), 0);

To create XML type instances, see Teradata XML.