Collection Functions

TODO.

Functions of this group can be used as for SET so for ARRAY field types.

APPEND_ITEMS( set, v1, v2, … )

Appends one or more values into the collection. Usually is used in the UPDATE command to append new items into the existed collection.

UPDATE T 
SET f1 = 5, f2 = 'aaa', fldSet = APPEND_ITEMS( fldSet, 4, 58, 98 )
WHERE recid = 57

REMOVE_ITEMS( set, v1, v2, … )

Removes one or more values from the collection. Usually is used in the UPDATE command to append new items into the existed collection.

UPDATE T 
SET f1 = 5, f2 = 'aaa', fldSet = REMOVE_ITEMS( fldSet, 4, 58 )
WHERE recid = 57

Functions of this group can be used as for SET so for ARRAY field types.

COUNT_ITEMS( set )

Returns count of items in the given collection.

SELECT f1, COUNT_ITEMS( fldSet )
FROM T
WHERE COUNT_ITEMS( fldSet ) > 10

INCLUDE_ITEM( set, v )

Returns TRUE if the collection contains v.

SELECT RecID, fldSet
FROM T
WHERE INCLUDE_ITEM( fldSet, 58 )

FIRST_ITEM( set )

Returns the first item of the array.

SELECT *
FROM T
WHERE FIRST_ITEM(fldSalaries) > 250   

LAST_ITEM( set )

Returns the last item of the array.

SELECT *
FROM T
WHERE LAST_ITEM(fldSalaries) > 1000   

FIND_ITEM( set, v )

Returns the position (1-based) of given item in the ARRAY. If not found then returns zero.

SELECT *
FROM T
WHERE POSITION_ITEM(fldSalaries, 100) = 5