15.00 - CARDINALITY - 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

CARDINALITY

Purpose  

Returns an integer representing the number of elements in an ARRAY data type that currently have assigned values, or the number of elements that are initialized within a specific scope reference.

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
  • scope_reference

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

    An explicit scope that includes uninitialized elements is acceptable input when an ARRAY expression is specified in a CARDINALITY function.

    ANSI Compliance

    CARDINALITY for a one-dimensional (1-D) ARRAY data type is ANSI SQL:2011 compliant.

    CARDINALITY for a multidimensional (n-D) ARRAY data type is a Teradata extension to the ANSI SQL:2011 standard.

    Argument Type

    The array_expr argument is an expression that evaluates to an ARRAY data type that is currently defined in the system.

    Result Type and Value

    CARDINALITY returns an integer value that represents the number of elements in the ARRAY that currently have assigned values. This count includes elements that are NULL. Since an ARRAY value may have fewer element values assigned than defined for its maximum size n, the CARDINALITY function may return a value that is smaller than n.

    The function returns 0 if the array argument is empty (that is, it does not have any elements assigned). If the array argument is NULL, then CARDINALITY returns NULL as the result.

    Even though an ARRAY function normally returns an error if any uninitialized elements fall within the range of the specified scope_reference in any of the ARRAY input arguments, an ARRAY expression specified within a CARDINALITY function behaves differently: an explicit scope that includes uninitialized elements is an acceptable input when an ARRAY expression is specified in a CARDINALITY function.

    Examples

    In the following example, an integer value is returned that represents the number of elements in the 1-D ARRAY that are currently 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 currently 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 currently 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));