Conditional expressions (CASE expressions) allow users to use the IF ... THEN ... ELSE structure in SQL statements without calling stored procedures.
Conditional expression syntax
CASE { simple_case_expression
| searched_case_expression
}
[ ELSE else_expr ]
END
Simple conditional expression simple_case_expression
expr
{ WHEN comparison_expr THEN return_expr }...
Searched conditional expression searched_case_expression
{ WHEN condition THEN return_expr }...
Usage rules
Validating conditions
In a simple conditional expression, OceanBase Database searches for the first comparison_expr that matches expr in the WHEN ... THEN clause and returns the corresponding return_expr. If no WHEN ... THEN clause matches the condition and an ELSE clause exists, OceanBase Database returns else_expr. Otherwise, it returns NULL.
In a searched conditional expression, OceanBase Database searches from left to right until a condition is met and returns the corresponding return_expr. If none of the conditions are met and an ELSE clause exists, OceanBase Database returns else_expr. Otherwise, it returns NULL.
Evaluating conditions
OceanBase Database uses short-circuit evaluation. For a simple conditional expression, it evaluates the value of each comparison_expr one by one before comparing it with expr, rather than evaluating all comparison_expr values at once. Therefore, if the previous comparison_expr is equal to expr, OceanBase Database will not evaluate the next comparison_expr. For a searched conditional expression, it evaluates each condition sequentially. If the previous condition is true, OceanBase Database will not evaluate the next condition.
Data types
For a simple conditional expression, the data types of expr and all comparison_expr values must be the same (e.g., CHAR, VARCHAR2, and NCHAR; NVARCHAR2, NUMBER, and BINARY_FLOAT; BINARY_DOUBLE) or all must be numeric. If all return expressions are numeric, OceanBase Database selects the highest-priority data type, explicitly converts other parameters to this type, and returns the value of this type.
For both simple and searched conditional expressions, the data types of all return_exprs must be the same (e.g., CHAR, VARCHAR2, and NCHAR; NVARCHAR2, NUMBER, and BINARY_FLOAT; BINARY_DOUBLE) or all must be numeric. If all return expressions are numeric, OceanBase Database selects the highest-priority data type, explicitly converts other parameters to this type, and returns the value of this type.
Examples
Simple conditional expression example
SELECT cust_last_name,
CASE sale_quantity
WHEN 100 THEN 'Low'
WHEN 500 THEN 'High'
ELSE 'Medium' END AS quantity
FROM customer
ORDER BY cust_last_name, quantity;
Searched conditional expression example
SELECT AVG(CASE WHEN e.salary > 6000 THEN e.salary
ELSE 6000 END) "Average Salary" FROM employees e;