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
Required: Yesvalue
Required: Yesvalue_if_naIFERROR
Required: Yesvalue
Required: Yesvalue_if_errorNAnone
ISERROR
Required: Yesvalue
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
IFNA | the value or the fallback | Returns the fallback when the value is not available (null). A value that is present, even 0 or empty text, is returned as it is | IFNA(NA(), "none") |
IFERROR | the value or the fallback | Returns the fallback when the value is null, as IFNA does. Coming: a calculation that fails, such as DIVIDE(1, 0), is not caught | IFERROR(5, "bad") |
NA | an unavailable value | Produces a value that is not available, which is returned as null | NA() |
ISERROR | true or false | True when the value is null (not available). Coming: a calculation that fails is not caught | ISERROR(NA()) |
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 | The wrong number of arguments | Give both the value and the fallback |
HTQL-8002 | A 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
IFNAis the function to use for a value that is not available.- Coming: catching a failed calculation.
IFERROR,IFNAandISERRORread 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!andIFERROR(FIND("z", "hello"), "not found")answers[HTQL-8003] #VALUE!.IFdoes 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 inDIVIDE(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.