CARDINALITY scalar function

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

Read syntax diagramSkip visual syntax diagramCARDINALITY( array-expression)

The schema is SYSIBM.

array-expression
Start of changeAn SQL variable, SQL parameter, or global variable of an array type, or a CAST specification that specifies an SQL variable, SQL parameter, global variable, or parameter marker as the source value.End of change
The result of the CARDINALITY function is as follows:
  • For an ordinary array, the result is the highest array index for which the array has an assigned element. Elements that have been assigned the null value are considered to be assigned elements.
  • For an associative array, the result is the actual number of unique array index values that are defined in array-expression.
  • For an empty array, the result is 0.

The data type of the result is BIGINT.

The result can be null; if array-expression is null, the result is the null value.

Example 1: Suppose that the array RECENT_CALLS is defined and contains a record of recent calls. RECENT_CALLS contains three elements. The following SET statement assigns the number of calls that have been stored in the array so far to SQL variable HOWMANYCALLS:

SET HOWMANYCALLS = CARDINALITY(RECENT_CALLS);

After the statement executes, HOWMANYCALLS contains 3.

Example 2: Suppose that the associative array variable CANADACAPITALS of array type CAPITALSARRAY contains all of the capitals for the 10 provinces and three territories in Canada, as well as the capital of the country, Ottawa. The following SET statement assigns the cardinality of CANADACAPTITALS to SQL variable NUMCAPITALS.

SET NUMCAPITALS = CARDINALITY(CANADACAPITALS) ;

After the statement executes, CANADACAPITALS contains 14.