# CARDINALITY scalar function

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

The schema is SYSIBM.

`array-expression`- An 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.

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