Returns the number of distinct elements in array_expr, optionally matching a specific input value.
System function syntax:
one of the following:
The element type of the array must be a numeric or character data type.
an optional ARRAY scope reference. See “ARRAY Scope Reference” on page 198.
an optional expression to be matched by one or more elements in the array.
This is a Teradata extension to the ANSI SQL:2011 standard.
ARRAY_COUNT_DISTINCT takes an array expression as an argument and returns a value that represents one of two possible outputs:
If you specify a scope reference, the function is applied only to the elements within the limits of the given scope. You can use ARRAY_COUNT_DISTINCT with arrays of numeric as well as character element types.
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.
The result type is an INTEGER.
Consider the following 1-D ARRAY data type and table.
CREATE TYPE item_price AS DECIMAL(7,2) ARRAY;
CREATE TABLE inventory (itemkind INTEGER,
In the following query, ARRAY_COUNT_DISTINCT returns the number of elements whose value is equal to 100 within the scope reference of the regular_price array.
SELECT ARRAY_COUNT_DISTINCT(regular_price, 5, 10, 100) FROM inventory;
The following is the same query using method-style syntax.
SELECT regular_price.ARRAY_COUNT_DISTINCT(5, 10, 100) FROM inventory;
The following query returns the number of distinct elements in the regular_price array.
SELECT ARRAY_COUNT_DISTINCT(regular_price)FROM inventory;
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 (
In the following query, ARRAY_COUNT_DISTINCT returns the number of elements whose value is equal to 100 within the specified scope of the shot1 array.
SELECT ARRAY_COUNT_DISTINCT(shot1, NEW arrayVec(5,5), NEW arrayVec(10,10), 100) FROM seismic_data;