MAX_CARDINALITY scalar function

The MAX_CARDINALITY function returns a value of type BIGINT representing the maximum number of elements that an array can contain. This is the cardinality that was specified in the CREATE TYPE statement for the ordinary array type.

Read syntax diagramSkip visual syntax diagram MAX_CARDINALITY ( array-expression )

The schema is SYSIBM.

array-expression
An SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type, or a column in a Hadoop table defined as an array.

Result

The result can be null; if the argument is an associative array, the result is the null value.

Example

  1. Return the maximum cardinality of the RECENT_CALLS array variable of array type PHONENUMBERS:
       SET LIST_SIZE = MAX_CARDINALITY(RECENT_CALLS)
    The SQL variable LIST_SIZE is set to 50, which is the maximum cardinality that the array type PHONENUMBERS was defined with.
  2. Return the maximum cardinality in the array from a Hadoop table.
    CREATE HADOOP TABLE t1 (c1 INT, c2 DOUBLE ARRAY[5]);
SELECT c1, MAX_CARDINALITY(c2) FROM t1;