Collection functions
Work with lists as values.
Syntax
Write the function's name and the list, with the value or second list it asks for.
FIRST(list)
LAST(list)
NTH(list, index)
POSITION(list, value)
LENGTH(list)
REVERSE(list)
APPEND(list1, list2)
SLICE(list, start, [length])
PUSH(list, value)
FLATTEN(list)
UNION_DISTINCT(list1, list2)
INTERSECTION(list1, list2)
MINUS(list1, list2)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
FIRST
Required: YeslistLAST
Required: YeslistNTH
Required: Yeslist
Required: YesindexPOSITION
Required: Yeslist
Required: YesvalueLENGTH
Required: YeslistREVERSE
Required: YeslistAPPEND
Required: Yeslist1
Required: Yeslist2SLICE
Required: Yeslist
Required: Yesstart
Required: NolengthPUSH
Required: Yeslist
Required: YesvalueFLATTEN
Required: YeslistUNION_DISTINCT
Required: Yeslist1
Required: Yeslist2INTERSECTION
Required: Yeslist1
Required: Yeslist2MINUS
Required: Yeslist1
Required: Yeslist2
Examples
A customer's tags held once, then counted, read by position and searched.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
FIRST | list | A list holding the first item | FIRST([3, 1, 2]) |
LAST | list | A list holding the last item | LAST([3, 1, 2]) |
NTH | the item | The item at that position | NTH([10, 20, 30], 1) |
POSITION | whole number | The position of the value, counting from 0, or -1 when it is not in the list | POSITION([10, 20, 30], 20) |
LENGTH | whole number | How many items | LENGTH([1, 2, 3]) |
REVERSE | list | The list in reverse order | REVERSE([1, 2, 3]) |
APPEND | list | The two lists joined into one | APPEND([1, 2], [3, 4]) |
SLICE | list | A part of the list | SLICE([10, 20, 30, 40], 1, 2) |
PUSH | list | The list with the item at the end | PUSH([1, 2], 3) |
FLATTEN | list | One list with the items of each inner list, one level deep | FLATTEN([[1, 2], [3]]) |
UNION_DISTINCT | list | The first list, then the items of the second that are not already in it. Coming: a repeat inside the first list is kept: UNION_DISTINCT([1, 1, 2], [2, 3, 3]) answers [1,1,2,3] | UNION_DISTINCT([1, 2], [2, 3]) |
INTERSECTION | list | The items that are in both lists | INTERSECTION([1, 2, 3], [2, 3, 4]) |
MINUS | list | The items of the first list that are not in the second | MINUS([1, 2, 3], [2]) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 | An argument is missing, such as a list with no value to add | Give the list and the value |
HTQL-2001 | NTH was given a position past the end of the list | Use a position from 0 to one less than the length |
HTQL-5005 | NTH was given a negative position | Use a position of 0 or more |
Limitations
- Positions in
NTHandPOSITIONcount from 0. FIRSTandLASTreturn a list holding the one item. UseNTHfor the item itself.- These functions work on lists.
LENGTHalso counts the characters of text; a number or null is refused withHTQL-2001. - Coming: a variable that holds a list of records, written bare as
LET rows = [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}], is read apart at the commas inside the records:LENGTH(rows)answers 4, andNTH(rows, 0)answers{"sku": "A1. Put the list inside a record, as inLET order = {"rows": […]}, and read it asorder.rows.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use LENGTH to count the rows a LOOKUP kept in a variable.
- Use UNION_DISTINCT to merge two lists without repeats.