CREATE TYPE Syntax Elements (Distinct Form) - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
SYSUDTLIB
An optional specification of the containing database for UDT_name, which must always be SYSUDTLIB.
UDT_name
Name of the distinct UDT to be created.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
See Teradata Vantage™ - SQL Fundamentals, B035-1141 for the rules for naming database objects.
Creating a distinct type consumes two names in the TVMNameI name space for SYSUDTLIB:
  • The first name consumed corresponds to the name of the UDT itself.
  • The second name corresponds to the system-generated UDF.

    The system constructs this name as explained in the topic on naming conventions.

You cannot create a UDF within the SYSUDTLIB database with the same specific name or same routine signature.
If such a UDF already exists in SYSUDTLIB at the time you submit a CREATE TYPE request, then the request fails because a routine with the same signature already exists.
data_type
Predefined data type on which the distinct UDT is based.
You cannot specify the following data types:
  • PERIOD(DATE), PERIOD(TIME), and PERIOD(TIMESTAMP)
  • JSON
  • XML and XMLTYPE
  • ST_GEOMETRY
  • DATASET
server_character_set
If data_type is a character type, then this is the server character set used by data_type , otherwise you cannot specify anything.
The purpose of the CHARACTER SET clause is to influence the system-generated routines.
The CHARACTER SET clause causes the following things to occur when the system creates the distinct type named UDF_name:
  • The system-generated CAST routines are registered to cast to and cast from a character source type of the specified server character set.
  • The system-generated ORDERING routine is registered to map to a character source type of the specified server character set.
  • The system-generated tosql and fromsql TRANSFORM routines are registered to transform the UDT to and from a character source type of the specified server character set.
If you know that all database transactions occur in languages that can be expressed using the LATIN character set, specify LATIN. Otherwise, for example, if some or all transactions use non-LATIN or multibyte characters, specify UNICODE. See Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.
You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.
FINAL
Mandatory keyword required for the definition of all distinct UDTS.
The FINAL keyword indicates that you cannot create subtypes of the type being defined.