CARDINALITY scalar function

The CARDINALITY function returns a value of type BIGINT representing the number of elements of an array.

Read syntax diagramSkip visual syntax diagram 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.

Result

For an:
  • Ordinary array, the returned value is the highest array index for which the array has an assigned element. This includes elements that have been assigned the null value.
  • Associative array, the returned value is the actual number of unique array index values defined in the array expression.

The function returns 0 if the array is empty. The result can be null; if the argument is null, the result is the null value.

Examples

  1. Return the number of calls that have been stored in the recent calls list so far:
       SET HOWMANYCALLS = CARDINALITY(RECENT_CALLS)
    The SQL variable HOWMANYCALLS contains the value 3.
  2. Assume that the associative array variable CAPITALS of array type CAPITALSARRAY contains all of the capitals for the 10 provinces and 3 territories in Canada as well as the capital of the country, Ottawa. Return the cardinality of the array variable:
       SET NUMCAPITALS = CARDINALITY(CAPITALS)
    The SQL variable NUMCAPITALS contains the value 14.