Icon CARDINALITY

Returns the cardinality of an array.

Syntax
CARDINALITY(<ArrayExpression>)

<ArrayExpression> =

Any array of type:
CHARACTER|CHAR
CHARACTER VARYING|VARCHAR
GUID
BYTE
BYTE VARYING|VARBYTE
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT
DECIMAL|NUMERIC
DATE
TIME
TIMESTAMP
INTERVAL YEAR
INTERVAL YEAR TO MONTH
INTERVAL MONTH
INTERVAL DAY
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL DAY TO MSECOND
INTERVAL HOUR
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL HOUR TO MSECOND
INTERVAL MINUTE
INTERVAL MINUTE TO SECOND
INTERVAL MINUTE TO MSECOND
INTERVAL SECOND
INTERVAL SECOND TO MSECOND
INTERVAL MSECOND

Returns
INTEGER

Usage
The CARDINALITY function returns the index (1-based) of the highest defined array element in an array. The highest defined array element is the highest array element that has been referenced in the array. For example, if you define an array with a maximum cardinality of 10, and reference the fifth (5) element in the array, then the CARDINALITY function will return 5 for the array.

Information ElevateDB currently only supports the use of arrays in SQL/PSM routines and does not support arrays as column types.

Examples
-- This script loops through the Customer table and
-- populates an array with the CustNo column value
-- for each row

SCRIPT
BEGIN
   DECLARE Done BOOLEAN DEFAULT False;
   DECLARE TotalRows INTEGER DEFAULT 0;
   DECLARE CustCursor CURSOR FOR CustStmt;
   DECLARE CustArray INTEGER ARRAY [56];

   SET LOG MESSAGE TO CAST(CARDINALITY(CustArray) AS VARCHAR);

   PREPARE CustStmt FROM 'SELECT CustNo,
                          Company
                          FROM Customer';

   OPEN CustCursor;

   WHILE (NOT EOF(CustCursor)) DO
      SET TotalRows=TotalRows+1;
      FETCH NEXT FROM CustCursor INTO CustArray[TotalRows];
      SET PROGRESS TO TRUNC((TotalRows/ROWCOUNT(CustCursor))*100);
   END WHILE;

   SET LOG MESSAGE TO CAST(CARDINALITY(CustArray) AS VARCHAR);

   CLOSE CustCursor;
END

SQL 2003 Standard Deviations
This function deviates from the SQL 2003 standard in the following ways:

DeviationDetails
Referenced ElementsElevateDB sets the cardinality of an array on any array access to a specific index, not just the assignment of a value to a specific index.
Image