15.10 - Multidimensional Array Options

15.10 - Multidimensional Array Options - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Product

Teradata Database

Release Number

15.10

Published

December 2015

Language

English (United States)

Last Update

2018-06-05

dita:mapPath

SQL_DDL_15_10.ditamap

dita:ditavalPath

ft:empty

array_type_name: The name of the multidimensional ARRAY type to be created.

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.

SYSUDTLIB

Optionally, the name of the containing database for array_type name.

The database name is SYSUDTLIB.

data type

The data type on which the multidimensional ARRAY type is based.

The element types of an array must be chosen from an existing Teradata Database data type including Distinct UDTs, Structured UDTs, and Period data types.

You can specify any supported Teradata Database predefined data types, except for:

BLOB
CLOB
Distinct and structured LOB-based UDT
Geospatial
One-dimensional ARRAY types
Multidimensional ARRAY types
JSON
XML and XMLTYPE

You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.

For a description of the predefined data types, see SQL Data Types and Literals, B035-1143.

ARRAY

The multidimensional ARRAY type is to be created using Teradata syntax.

DEFAULT NULL

Initialize all of the elements of array_type_name to null when the type is created.

Dimensions

You must specify a minimum of 2 dimensions for a multidimensional ARRAY type. The maximum number of dimensions you can specify is 5.

The maximum size of a type created as a multidimensional ARRAY must not exceed the maximum size of a Teradata Database row, which is 64,256 bytes. The maximum size of the auto-generated transform string for the ARRAY must also not exceed this limitation. The size of an auto-generated transform string for the ARRAY is limited to the maximum size of a DBS VARCHAR type. Therefore, the overall limit on the number of elements is dependent on the size of the elements and the size of the transform. For details on how the size of the transform is calculated, see the appendix topic "Transform Input/Output Strings for ARRAY/VARRAY UDTs" in SQL Data Types and Literals.

lower_bound: A signed INTEGER number that defines the lower bound for a multidimensional ARRAY type dimension. The lower boundary can be a negative number and must be less than or equal to upper_bound.
upper_bound: A signed INTEGER number that defines the upper bound for a multidimensional ARRAY type dimension. The upper boundary can be a negative number and must be greater than or equal to lower_bound.
maximum_size: The maximum size of a dimension of a type created as a multidimensional ARRAY. The maximum size of the current dimension of the multidimensional ARRAY must not exceed the maximum size of a Teradata Database row, or 64,256 bytes.; When you specify this option, the lower bound for the current dimension is implicitly defined to be 1.; This must be an unsigned INTEGER value.