CARDINALITY Example: Return Integer that Represents Number of Elements in 1-D ARRAY - Teradata Vantage

In the following example, an integer value is returned that represents the number of elements in the 1-D ARRAY that are filled in.

CREATE TYPE phonenumbers AS CHAR(10) ARRAY[20];
CREATE TABLE employee_info (eno INTEGER, phonelist phonenumbers);
SELECT CARDINALITY(phonelist)
FROM employee_info;

The CARDINALITY function can also be used to return the number of elements that are initialized within a specific scope reference. For example, the following query returns the number of initialized elements within the range from 5 to 10 of the phonelist array.

SELECT CARDINALITY(phonelist, 5, 10);

Consider the following 2-D ARRAY data type:

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

In the following example, an integer value is returned that represents the number of elements in the 2-D ARRAY that are filled in.

SELECT CARDINALITY(shot1) FROM seismic_data;

The following shows the same query using method-style syntax:

SELECT shot1.CARDINALITY() FROM seismic_data;

The following query returns the number of initialized elements within the range from 5 to 10 on each dimension of the phonelist array.

SELECT CARDINALITY(phonelist, NEW ArrayVec(5,5), NEW ArrayVec(10,10));