Check the cardinality of a LIST collection

At times you might want to add an element at the end of a LIST. In this case, you can use the cardinality() function to find the number of elements in a LIST and then enter a position that is greater than the value cardinality() returns.

HCL OneDB™ allows you to use the cardinality() function with a collection that is stored in a column but not with a collection that is stored in a collection variable. In an SPL routine, you can check the cardinality of a collection in a column with a SELECT statement and return the value to a variable.

Suppose that in the numbers table, the evens column of the row whose id column is 99 still contains the collection LIST {2,4,6,8,10}. This time, you want to add the element 12 at the end of the LIST. You can do so with the SPL procedure end_of_list, as the following figure shows.

Figure 1: The end_of_list SPL procedure.

CREATE PROCEDURE end_of_list()

   DEFINE n SMALLINT;
   DEFINE list_var LIST(INTEGER NOT NULL);

   SELECT CARDINALITY(evens) FROM numbers INTO n
      WHERE id = 100;

   LET n = n + 1;

   SELECT evens INTO list_var FROM numbers
      WHERE id = 100;

   INSERT AT n INTO TABLE(list_var) VALUES(12);

END PROCEDURE;

In end_of_list, the variable n holds the value that cardinality() returns, that is, the count of the items in the LIST. The LET statement increments n, so that the INSERT statement can insert a value at the last position of the LIST. The SELECT statement selects the collection from one row of the table into the collection variable list_var. The INSERT statement inserts the element 12 at the end of the list.