ARRAY_MIN - Teradata Database

ARRAY_MIN

Purpose  

Returns the minimum of all element values in array_expr or the minimum 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_MIN takes an array expression as an argument and returns an element value that represents the minimum 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_MIN 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 are 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 minimum of all the element values in the regular_price array.

SELECT ARRAY_MIN(regular_price) FROM inventory;

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

SELECT ARRAY_MIN(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 minimum of all the element values in the shot1 array.

SELECT ARRAY_MIN(shot1) FROM seismic_data;

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

SELECT ARRAY_MIN(shot1, NEW arrayVec(5,5), NEW arrayVec(10,10)) 

FROM seismic_data;

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

SELECT shot1.ARRAY_MIN(NEW arrayVec(5,5), NEW arrayVec(10,10)) 

FROM seismic_data;