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.