ARRAY Scope Reference Rules | Teradata Vantage - Rules - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢
When the optional scope_reference parameter is specified in an ARRAY function, the following rules apply:
  • You can pass two integer values or two ArrayVec values for the scope_reference. If the ARRAY function is called with one-dimensional ARRAY arguments, you can pass a combination of integer and ArrayVec values for the scope_reference. For example, the scope_reference can be <integer_value, ArrayVec_value > or <ArrayVec_value, integer_value >.
  • If you use an ArrayVec instance for specifying the upper or lower bounds of a scope_reference, the following are not allowed:
    • Passing in NULL for any of the ArrayVec elements that are needed to determine the bound of the array argument.
    • Passing an insufficient number of elements in the ArrayVec to determine the bound of the array argument.
    • Passing too many elements in the ArrayVec to determine the bound of the array argument.
  • For all ARRAY functions that define the RETURNS NULL ON NULL INPUT clause, if one of the optional parameters to define the scope_reference is passed as NULL, then NULL is returned as the result value.
  • ARRAY functions that are called with a scope_reference specified are considered to have their scope explicitly defined, so these functions return an error if any uninitialized elements fall within the range of the scope_reference in any of the ARRAY input arguments. The CARDINALITY function is an exception to this rule because an explicit scope that includes uninitialized elements is an acceptable input to the function.

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:

  • If you call an ARRAY function without specifying a scope_reference, the default scope used is the number of initialized elements in the input ARRAY argument. If an ARRAY function accepts two ARRAY arguments and the number of initialized elements is different between the two arrays, then an error is returned.
  • When an ARRAY function returns an ARRAY value, the resultant ARRAY value will have the same number of initialized elements as the input ARRAY argument.