Conditional expression functions return values based on logical operations using each expression passed to the function. For example, the `BOOLOR` function takes two numeric expressions and returns True if either (or both) of the expressions evaluate to a True (non-zero) value.
Snowflake provides conditional functions that allow you to perform conditional logic and handle null values in your queries. Here are some commonly used conditional functions in Snowflake:
1. CASE: Evaluates a series of conditions and returns a result based on the first matching condition.
Example:
```
sqlCopy code
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
```
2. COALESCE: Returns the first non-null value from a list of expressions.
Example: **`COALESCE(column1, column2, 'N/A')`** returns the value of column1 if it's not null, otherwise the value of column2, and if both are null, returns 'N/A'.
3. NULLIF: Compares two expressions and returns null if they are equal, or the first expression otherwise.
Example: **`NULLIF(column1, 0)`** returns null if column1 equals 0, otherwise returns the value of column1.
4. DECODE: Performs conditional value substitution based on multiple conditions.
Example:
```
sqlCopy code
DECODE(column1,
value1, result1,
value2, result2,
default_result)
```
5. IFNULL: Returns the second expression if the first expression is null.
Example: **`IFNULL(column1, column2)`** returns column1 if it's not null, otherwise returns the value of column2.
6. NVL: Returns the second expression if the first expression is null.
Example: **`NVL(column1, column2)`** returns column1 if it's not null, otherwise returns the value of column2.
7. NULLIFZERO: Returns null if the input expression is zero; otherwise, returns the input expression.
Example: **`NULLIFZERO(column1)`** returns null if column1 is zero, otherwise returns the value of column1.
These are some commonly used conditional functions in Snowflake. These functions help in handling null values, performing conditional logic, and substituting values based on specific conditions in your queries. The Snowflake documentation provides more detailed explanations and examples for each of these functions.
