15.00 - ARRAY_GET - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

ARRAY_GET

Purpose  

Returns the element value in array_expr that corresponds to the specified index position.

Syntax  

System function syntax:

Method-style syntax:

where:

 

Syntax element…

Specifies…

array_expr

one of the following:

  • A reference to an ARRAY column
  • An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195.
  • A UDF expression
  • A UDM expression
  • array_index

    one of the following:

  • an integer value.
  • an array instance of the predefined array type ArrayVec with a comma-separated list of integer values to define the bounds. For details, see “ARRAY Scope Reference” on page 198.
  • The value of array_index must be within the limits of array_expr.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Usage Notes  

    ARRAY_GET takes an array expression as an argument and returns the element value in array_expr that corresponds to the position specified by array_index.

    If array_expr is a one-dimensional ARRAY type, the index of the element to be located is defined by an INTEGER or an ArrayVec type that must be within the defined boundaries of array_expr.

    If array_expr is a multidimensional ARRAY type, the index of the element to be located is defined using the predefined array type ArrayVec. For details on the ArrayVec type, see “ARRAY Scope Reference” on page 198. The number of dimensions defined by ArrayVec must be between 2 and 5 (the maximum number of dimensions supported) and must correspond to the number of dimensions in array_expr. The values for each dimension are separated by a comma and they must be within the defined boundaries of array_expr.

    If the value of array_index references an element of the ARRAY which is in an uninitialized state, an error is returned. To avoid referencing an element of the ARRAY that is uninitialized, set any uninitialized elements to NULL. You can do this with the OEXTEND method. See “OEXTEND” on page 272.

    If array_expr is NULL, the function returns NULL.

    Result Type

    The result type is the same as the element type of the array argument.

    Example

    Consider the following 1-D ARRAY data type and table.

    CREATE TYPE item_price AS VARRAY(50) OF DECIMAL(7,2);
     
    CREATE TABLE inventory (itemkind INTEGER,
                            regular_price item_price,
                            sale_price item_price);

    In the following query, ARRAY_GET returns the element value located in position 40 of the regular_price array.

    SELECT ARRAY_GET(regular_price,40) FROM inventory;

    Example  

    Consider the following 2-D ARRAY data type and table.

    CREATE TYPE shot_ary AS INTEGER ARRAY[1:50][1:50];
     
    CREATE TABLE seismic_data (
       id INTEGER,
       shot1 shot_ary,
       shot2 shot_ary);

    In the following query, ARRAY_GET returns the element value located in position [5][10] of the shot1 array.

    SELECT ARRAY_GET(shot1, NEW arrayVec(5,10)) FROM seismic_data;

    The following is the same query using method-style syntax.

    SELECT shot1.ARRAY_GET(NEW arrayVec(5,10)) FROM seismic_data;