15.00 - ARRAY_MAX - 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_MAX

Purpose  

Returns the maximum of all element values in array_expr or the maximum of the elements within the specified scope.

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
  • The element type of the array must be a numeric data type.

    scope_reference

    an optional ARRAY scope reference. See “ARRAY Scope Reference” on page 198.

    ANSI Compliance

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

    Usage Notes  

    ARRAY_MAX takes an array expression as an argument and returns an element value that represents the maximum value of all the elements composing the array. If you specify a scope reference, the function is applied only to the elements within the limits of the given scope.

    The affected elements cannot be filtered through a conditional expression. Therefore, a SELECT statement involving ARRAY_MAX must not contain an array relational expression in the WHERE clause.

    If a NULL element is encountered in the array argument, it is ignored and not considered when doing the calculation. If the array argument contains any elements that is in an uninitialized state, an error is returned. Use a scope reference to avoid referencing a range of the array with uninitialized elements, or 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 DECIMAL(7,2) ARRAY[20];
     
    CREATE TABLE inventory (itemkind INTEGER,
                            regular_price item_price,
                            sale_price item_price);

    The following query returns an element value, which is the maximum of all the element values in the regular_price array.

    SELECT ARRAY_MAX(regular_price) FROM inventory;

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

    SELECT regular_price.ARRAY_MAX() FROM inventory;

    The following query returns an element value, which is the maximum of the element values within the specified scope of the regular_price array.

    SELECT ARRAY_MAX(regular_price, 5, 10)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);

    The following query returns an element value, which is the maximum of all the element values in the shot1 array.

    SELECT ARRAY_MAX(shot1) FROM seismic_data;

    The following query returns an element value, which is the maximum of the element values within the specified scope of the shot1 array.

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