Skip to main content

Conditional Functions

Scalar functions for conditional logic and branching that can be used in any expression context.

if(condition, then) / if(condition, then, else)​

Returns different values based on a boolean condition.

Parameters​

  • condition - Boolean expression to evaluate
  • then - Expression to return when condition is true
  • else (optional) - Expression to return when condition is false

Description​

The if() function evaluates the condition and returns the then expression if the condition is true. When used with two parameters, it returns null if the condition is false. When used with three parameters, it returns the else expression if the condition is false.

Both the then and else expressions must be of the same type when the three-parameter form is used. The function provides a concise way to implement conditional logic within expressions.

case(when: then, when: then, ...)​

Returns values based on multiple conditions evaluated in order.

Parameters​

  • when: then - Variable number of condition-value pairs

Description​

The case() function evaluates multiple condition-value pairs in order and returns the value associated with the first condition that evaluates to true. Unlike SQL's CASE WHEN syntax, Hamelin uses function syntax with colon-separated pairs.

Each condition must be a boolean expression, and all values must be of the same type. If no condition matches, the function returns null. The conditions are evaluated in the order they appear, so earlier conditions take precedence.

coalesce(...)​

Returns the first non-null value from a list of expressions.

Parameters​

  • ... - Variable number of expressions of the same type

Description​

The coalesce() function evaluates expressions from left to right and returns the first expression that is not null. If all expressions are null, it returns null. All expressions must be of the same type.

This function is commonly used for providing default values or handling null values in expressions. It's particularly useful when you want to fall back through a series of potentially null values to find the first valid one.