Conditional and conversion functions

Applies to VAST Cluster 5.5.

Casts, CASE, if, coalesce, nullif, greatest and least, and type checks.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Session and diagnostics

Function	What it does
error	Stop a query with your own error message, for example inside a CASE to reject rows that break a rule.

Converting types

Function	What it does
CAST and ::	Convert a value to another type with CAST(x AS type) or x::type: text to numbers, dates and lists, rounding, overflow and errors.
TRY_CAST	Convert a value to another type, returning NULL instead of an error when the value doesn't convert or doesn't fit.
typeof	Return the name of an expression's type, such as INTEGER or DECIMAL(2,1).
can_cast_implicitly	Check whether a value of one type converts to another type without an explicit CAST.

Choosing a value

Function	What it does
CASE	Return the value for the first condition that holds with CASE: searched and simple forms, result types, NULLs and pushdown.
if	Pick one of two values by a condition with if(), a short form of a CASE with one condition.

Replacing NULLs

Function	What it does
coalesce	Return the first argument that isn't NULL with coalesce(): result types, empty strings, errors and pushdown.
ifnull	Replace a NULL with a default value with ifnull(), the two-argument form of coalesce.
nullif	Turn a placeholder value into NULL with nullif(): equality rules, result type, NULLs and pushdown.

Largest and smallest argument

Function	What it does
greatest	Return the largest of its arguments within a row with greatest(): types, ordering rules, NULLs and pushdown.
least	Return the smallest of its arguments within a row with least(): types, ordering rules, NULLs and pushdown.