ARRAY Scope Reference
Purpose
References one or more elements of an ARRAY data type when invoking an ARRAY function.
Syntax
where:
Syntax element… |
Specifies… |
array_lower_bounds |
an integer value or an array instance of the predefined ARRAY type ArrayVec, with a comma-separated list of integer values to define the lower bounds. |
array_upper_bounds |
an integer value or an array instance of the predefined ARRAY type ArrayVec, with a comma-separated list of integer values to define the upper bounds. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Usage Notes
To reference one or more elements of an ARRAY data type when invoking an ARRAY function, you can use an integer value for 1-D arrays, but you must use the ArrayVec ARRAY data type for n-D arrays. The ArrayVec ARRAY data type is automatically created by the system and is defined as:
CREATE TYPE ArrayVec AS INTEGER ARRAY[1000];
For all ARRAY functions that contain an optional scope_reference or array_bound argument, you can use one or more instances of the ArrayVec ARRAY type to describe the boundaries of each dimension of an n-D ARRAY data type.
Rules
When the optional scope_reference parameter is specified in an ARRAY function, the following rules apply:
If your application requires operations between 2 arrays, consider creating these arrays using the DEFAULT NULL clause at creation time. This will prevent the system from returning errors due to illegal element references since all elements of the array will be initialized.
When the optional scope_reference parameter is not specified in an ARRAY function, the following rules apply:
Example
The following query returns the number of distinct elements within the range from 5 to 10 on each dimension of the phonelist array.
SELECT CARDINALITY(phonelist, NEW ArrayVec(5,5), NEW ArrayVec(10,10));