- A multidimensional type transform.
This is the fromsql and tosql functionality associated with the transform of a multidimensional ARRAY/VARRAY type. The multidimensional 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 multidimensional ARRAY/VARRAY.
See Transform Input/Output Strings for ARRAY/VARRAY Types for the formatting of the transformed output.
- A constructor function and constructor method.Teradata Database automatically generates a default constructor function and an additional constructor method for each multidimensional ARRAY/VARRAY type you create.
- The default constructor function takes no parameters and allocates a multidimensional ARRAY/VARRAY instance in Teradata Database with the scope defined by the cross product of all interval expressions in the multidimensional ARRAY/VARRAY data type, where the interval expressions are either in the form [lower_bound : upper_bound] or are in the form [maximum_size], where:
Element Description [ lower_bound : upper_bound ] Boundary range for the multidimensional array, where both lower_bound and upper_bound are signed INTEGER values. [ maximum_size ] Maximum size of the multidimensional array, up to 64,256 bytes. Teradata Database sets all the elements of the multidimensional ARRAY to an uninitialized state. This is different from setting the elements null.
Teradata Database aborts the request and 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 multidimensional 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 defined by the cross product of all of the interval expressions in the multidimensional ARRAY/VARRAY data type, where the interval expressions are either in the form [lower_bound : upper_bound] or are in the form [maximum_size], and initializes each element of the multidimensional ARRAY/VARRAY with the corresponding value that is passed to it. See the table in the previous bulleted item for the definitions of the multidimensional array boundary variables.
Teradata Database stores the element values of a multidimensional ARRAY/VARRAY in row-major order. Row-major means that the first dimension, which is leftmost in the scope specification, is the major dimension, and as you move toward the last dimension, the dimensions become less and less major. This applies to arrays of an arbitrary number of dimensions. Most modern computer languages use row-major storage.
The constructor method initializes the elements of the multidimensional ARRAY/VARRAY starting with the first element as defined in the scope specification and proceeds in row-major order through the elements.
Although there normally is an upper limit of 128 parameters for a constructor method, the number of parameters you can specify for a multidimensional ARRAY/VARRAY types is only limited by the maximum number of elements that can be defined for the multidimensional ARRAY/VARRAY type. If a multidimensional ARRAY/VARRAY has n total elements, and you pass more than n parameters to the constructor method, Teradata Database aborts the request and returns a message to the requestor.
- The default constructor function takes no parameters and allocates a multidimensional ARRAY/VARRAY instance in Teradata Database with the scope defined by the cross product of all interval expressions in the multidimensional ARRAY/VARRAY data type, where the interval expressions are either in the form [lower_bound : upper_bound] or are in the form [maximum_size], where:
- A multidimensional ARRAY/VARRAY type ordering.
Teradata Database provides basic ordering functionality for a newly created multidimensional ARRAY/VARRAY type.
Teradata Database provides this ordering functionality to avoid hashing issues and to enable multidimensional ARRAY/VARRAY types to be allowed as columns in SET tables.
You cannot make relational comparisons of multidimensional ARRAY/VARRAY values, though you can make relational comparisons of the individual elements of a multidimensional ARRAY/VARRAY type. See Teradata Vantage™ - Data Types and Literals, B035-1143 for details about how to do this.
You cannot specify a multidimensional 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
- A multidimensional ARRAY/VARRAY type casting.Teradata Database provides casting functionality for a multidimensional ARRAY/VARRAY type. The system provides two autogenerated casts for all multidimensional ARRAY/VARRAY types.
- VARCHAR to ARRAY/VARRAY
- ARRAY/VARRAY to VARCHAR
The format of the VARCHAR string as both the cast source and target value is the same as that of the tosql and fromsql transforms. See Transform Input/Output Strings for ARRAY/VARRAY Types for more information about the formats of these strings.
You can also create your own UDFs to perform casting operations between all or a subset of the elements of two multidimensional ARRAY/VARRAY types.
Once you have created a multidimensional ARRAY/VARRAY UDT, Teradata Database automatically generates the following additional UDT-related functionality for you.