System-Generated Default Functionalities For One Dimensional ARRAY and VARRAY UDTs - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

When you create a one-dimensional ARRAY/VARRAY UDT, Teradata Database automatically generates the following additional UDT-related functionality for you.

One-Dimensional Type Transform

This is the fromsql and tosql functionality associated with the transform of a one-dimensional ARRAY/VARRAY type. The one-dimensional ARRAY/VARRAY values are transformed to/from a VARCHAR(length) value, where length depends on the element type and the total number of elements defined in the one-dimensional ARRAY/VARRAY.

For the formatting of the transformed output, see Transform Input/Output Strings for ARRAY/VARRAY Types.

Constructor Function and Constructor Method

Teradata Database automatically generates a default constructor function and an additional constructor method for each one-dimensional ARRAY or VARRAY type you create.

The default constructor function takes no parameters and allocates a one-dimensional ARRAY/VARRAY type instance.

Teradata Database sets all the elements of the one-dimensional ARRAY/VARRAY to an uninitialized state. This is different from setting the elements null.

Teradata Database returns an error to the requestor if any element is accessed from the ARRAY/VARRAY.

Teradata Database sets all of the elements to an uninitialized state only if the one-dimensional ARRAY/VARRAY type has been created without specifying the DEFAULT NULL option.

The constructor method takes one or more parameters, up to the number of parameters as defined by the declared size of the one-dimensional ARRAY/VARRAY data type, and initializes each element of the one-dimensional ARRAY/VARRAY with the corresponding value that is passed to it.

Teradata Database stores the element values of a one-dimensional ARRAY/VARRAY sequentially, so you must pass the parameters to the constructor in sequential order. The constructor initializes the elements of a one-dimensional ARRAY/VARRAY starting with the first element, moving from left to right.

Although there normally is an upper limit of 128 parameters for a constructor method, the number of parameters you can specify for a one-dimensional ARRAY/VARRAY constructor method is only limited by the maximum number of elements that you have defined for the one-dimensional ARRAY/VARRAY type.

If a one-dimensional ARRAY/VARRAY has n total elements, and you pass more than n parameters to the constructor, Teradata Database returns an error to the requestor.

One-Dimensional ARRAY/VARRAY Type Ordering

Teradata Database provides very basic ordering functionality for a newly created one-dimensional ARRAY/VARRAY type.

Teradata Database provides this ordering functionality to avoid hashing issues and to enable one-dimensional ARRAY/VARRAY types to be allowed as columns in SET tables.

You cannot make relational comparisons of one-dimensional ARRAY/VARRAY values.

You cannot use any of the relational comparison operators on one-dimensional ARRAY data, nor can you specify a one-dimensional ARRAY/VARRAY column in any of the following SQL DML clauses:
  • INTERSECT set operator
  • MINUS set operator
  • UNION set operator
  • DISTINCT operator
  • WHERE clause
  • ORDER BY clause
  • GROUP BY clause
  • HAVING clause

One-Dimensional ARRAY/VARRAY Type Casting

Teradata Database provides casting functionality for a one-dimensional ARRAY/VARRAY type. The system provides two autogenerated casts for all one-dimensional ARRAY/VARRAY types:
  • VARCHAR to ARRAY/VARRAY
  • ARRAY/VARRAY to VARCHAR

The format of the VARCHAR string as both the case source and target value is the same as the format of the tosql and fromsql transforms. For more information about the formats of these strings, see Transform Input/Output Strings for ARRAY/VARRAY Types.

You can also create your own UDFs to perform casting operations between the elements of two one-dimensional ARRAY/VARRAY types.