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