When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To return values based on whether specific conditions are met, you may find these common expressions useful.
To evaluate a list of conditions and return a value based on the first match or true condition, use a
CASE expression, such as to enable if/else logic.
CASE has two formats:
- In its simple form,
CASEevaluates the list for a specific expression and returns its value, such as:
CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END
- In its searched form,
CASEevaluates a list of booleans and return the value of the first
TRUEcondition, such as:
CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END
For example, in its simple form to return a value ("one" or "two") based on a matching expression:
SELECT expression, CASE expression WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'many' END
If no conditions are true,
CASE returns the value of the
ELSE clause ("many", in this example), or
NULL if there is no
Note: To return a value based on whether a single condition is true, use an
To return the first non-null value in a list, use the
COALESCE expression as COALESCE(value1, value2, value3).
To return a value depending on whether a condition is true or false, use the
IF statement as IF(condition, true_value, false_value).
Note: If the condition is false and no false_value is specified, the
IF statement returns
IS NULL or IS NOT NULL
To test whether a value is empty or
NULL, use the
IS NULL or
IS NOT NULL operator:
- To test for empty values, use
- To test for non-empty values, use
IS NOT NULL.
For example, to test customer data for those with no addresses:
SELECT Customer_Name, Contact_Name, Address
WHERE Address IS NULL
NULL when an expression fails due to corrupt or invalid data, use the
TRY expression as TRY(expression).
TRY handles these errors:
- Division by zero
CASTor function argument
- Numeric value out of range