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

    condition

    Required: Yes
  • value_if_true

    Required: Yes
  • value_if_false

    Required: Yes
  • IFS

    condition

    Required: Yes
  • value

    Required: Yes
  • CHOOSE

    index

    Required: Yes
  • values

    Required: Yes
  • AND

    conditions

    Required: Yes
  • XOR

    conditions

    Required: Yes
  • OR

    conditions

    Required: Yes
  • NOT

    condition

    Required: Yes

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.

FunctionReturnsBehaviourForm
IFthe chosen valueReturns one of two valuesIF(5 > 3, "yes", "no")
IFSthe chosen valueReturns the value of the first test that is trueIFS(1 > 3, "a", 2 > 1, "b")
CHOOSEthe chosen valueReturns the value at that positionCHOOSE(2, "x", "y", "z")
ANDtrue or falseTrue when every test is trueAND(true, false)
XORtrue or falseTrue when an odd number of the tests are trueXOR(true, true)
ORtrue or falseTrue when any test is trueOR(true, false)
NOTtrue or falseThe opposite of the testNOT(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
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5001IF was given two arguments, such as IF(true, 1)Give it the test and both values
HTQL-1019The test given to IF is text or nullMake the test a comparison, or a number
HTQL-2001CHOOSE was given an index outside its values, such as CHOOSE(5, "x", "y")Use an index from 1 to the number of values
HTQL-2002No test in IFS was trueEnd the list with a test that is always true, such as true, and a default value
Limitations
  • IF takes exactly three arguments, and IFS an even number, at least two.
  • IF is a function, not a filter. Use FILTER to choose rows.
  • Coming: only the chosen value is worked out. Today IF and IFS work 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!, and IFS with a LENGTH of 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-1019 Not 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 after LET 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.