DB2 Version 9.7 for Linux, UNIX, and Windows

CARDINALITY scalar function


>>-CARDINALITY--(--array-variable--)---------------------------><

The schema is SYSIBM.

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

array-variable: An SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type.

For an ordinary array, the value returned by the CARDINALITY function is the highest array index for which the array has an assigned element. This includes elements that have been assigned the null value. For an associative array, the value returned by the CARDINALITY function is the actual number of unique array index values defined in array-variable.

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

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.
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.