Condition functions
Let a statement choose.
Syntax
Write the function's name and its conditions and values.
IF(condition, value_if_true, value_if_false)
IFS(condition, value)
CHOOSE(index, values)
AND(conditions)
XOR(conditions)
OR(conditions)
NOT(condition)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
IF
Required: Yescondition
Required: Yesvalue_if_true
Required: Yesvalue_if_falseIFS
Required: Yescondition
Required: YesvalueCHOOSE
Required: Yesindex
Required: YesvaluesAND
Required: YesconditionsXOR
Required: YesconditionsOR
Required: YesconditionsNOT
Required: Yescondition
Examples
An order ships free when it holds 10 or more items and the customer is a member.
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 |
|---|---|---|---|
IF | the chosen value | Returns one of two values | IF(5 > 3, "yes", "no") |
IFS | the chosen value | Returns the value of the first test that is true | IFS(1 > 3, "a", 2 > 1, "b") |
CHOOSE | the chosen value | Returns the value at that position | CHOOSE(2, "x", "y", "z") |
AND | true or false | True when every test is true | AND(true, false) |
XOR | true or false | True when an odd number of the tests are true | XOR(true, true) |
OR | true or false | True when any test is true | OR(true, false) |
NOT | true or false | The opposite of the test | NOT(true) |
IF returns a value and is not the FILTER clause. AND, OR and NOT are also the logical operators described under Expressions.
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-5001 | IF was given two arguments, such as IF(true, 1) | Give it the test and both values |
HTQL-1019 | The test given to IF is text or null | Make the test a comparison, or a number |
HTQL-2001 | CHOOSE was given an index outside its values, such as CHOOSE(5, "x", "y") | Use an index from 1 to the number of values |
HTQL-2002 | No test in IFS was true | End the list with a test that is always true, such as true, and a default value |
Limitations
IFtakes exactly three arguments, andIFSan even number, at least two.IFis a function, not a filter. Use FILTER to choose rows.- Coming: only the chosen value is worked out. Today
IFandIFSwork out every value first, so a value that fails stops the statement even when it is not chosen:RETURN {"a": IF(true, 1, DIVIDE(1, 0))}answers[HTQL-8002] #DIV/0!, andIFSwith aLENGTHof a null in a later branch answers[HTQL-2001] LENGTH expects an array or string, got Null. Write each value so that it is safe to work out whichever test is true. - The test is true or false, or a number (0 is false, anything else is true). Text or null as the test stops with
HTQL-1019Not implemented. - Coming: a variable that holds a single value, written bare in a test, is not read. After
LET total = 500,IF(total > 1000, "big", "small")answers "big", and afterLET status = "paid",IF(status = "paid", "ship", "hold")answers "hold". Until it is installed, hold the values in a record and read them with a dot,order.total, as the examples do.
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 IFS for several tests in a row rather than IFs inside IFs.
- Put the most specific test first in IFS.