Error handling functions

Keep a result readable when a value is missing.

Syntax

Write the function's name and its arguments.

IFNA(value, value_if_na)
IFERROR(value, value_if_error)
NA()
ISERROR(value)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • IFNA

    value

    Required: Yes
  • value_if_na

    Required: Yes
  • IFERROR

    value

    Required: Yes
  • value_if_error

    Required: Yes
  • NA

    none

  • ISERROR

    value

    Required: Yes

Examples

A customer record with no nickname: IFNA supplies "Guest", while a value that is present passes through.

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
IFNAthe value or the fallbackReturns the fallback when the value is not available (null). A value that is present, even 0 or empty text, is returned as it isIFNA(NA(), "none")
IFERRORthe value or the fallbackReturns the fallback when the value is null, as IFNA does. Coming: a calculation that fails, such as DIVIDE(1, 0), is not caughtIFERROR(5, "bad")
NAan unavailable valueProduces a value that is not available, which is returned as nullNA()
ISERRORtrue or falseTrue when the value is null (not available). Coming: a calculation that fails is not caughtISERROR(NA())
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-5001The wrong number of argumentsGive both the value and the fallback
HTQL-8002A calculation inside the call divided by zero, as in IFERROR(DIVIDE(1, 0), 0)Keep a zero out of the divisor, as in MAX(qty, 1)
Limitations
  • IFNA is the function to use for a value that is not available.
  • Coming: catching a failed calculation. IFERROR, IFNA and ISERROR read only a value that is null. A calculation that fails stops the statement before they can see it: RETURN {"a": IFERROR(DIVIDE(1, 0), 0)} answers [HTQL-8002] #DIV/0!, IFERROR(SQRT(-1.0), "n/a") answers [HTQL-8004] #NUM! and IFERROR(FIND("z", "hello"), "not found") answers [HTQL-8003] #VALUE!. IF does not help, because it works out both of its values (see Condition functions). Until it is installed, keep a zero out of the divisor, as in DIVIDE(total, MAX(qty, 1)).
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
  • Give a fallback that a reader will understand, such as "none".
  • Use ?? (null coalescing) for a value that may be null.