A case expression allows you to use IF ... THEN ... ELSE in an SQL statement without calling stored procedures.
Syntax
CASE { simple_case_expression
| searched_case_expression
}
[ ELSE else_expr ]
END
Simple case expressions
expr
{ WHEN comparison_expr THEN return_expr }...
Searched case expressions
{ WHEN condition THEN return_expr }...
Considerations
Validation of conditions
In a simple case expression, OceanBase Database searches for the first equivalent comparison_expr in WHEN ... THEN, using expr as the benchmark, and returns the corresponding return_expr. If WHEN ... THEN does not satisfy the condition, and the ELSE clause is specified, OceanBase Database returns else_expr. Otherwise, OceanBase Database returns NULL.
In a searched case expression, OceanBase Database searches data from left to right until condition is valid, and then returns return_expr. If none of the conditions are valid, and the ELSE clause is specified, the database returns else_expr. Otherwise, OceanBase Database returns NULL.
Calculation of conditions
OceanBase Database uses the short-circuit evaluation. For a simple case expression, the database calculates onecomparison_expr value for each comparison with expr, rather than calculating all comparison_expr values in one time. Therefore, if one comparison_expr value equals expr, OceanBase Database does not calculate subsequent comparison_expr values. For a searched case expression, the database sequentially validates each condition. If one condition is true, OceanBase Database will not validate a subsequent condition.
Data type
For a simple case expression, expr and all comparison_expr values must be of the same data type (such as CHAR, VARCHAR2 and NCHAR; NVARCHAR2, NUMBER and BINARY_FLOAT; BINARY_DOUBLE), or must be of numeric types. If all return expressions are of the numeric type, OceanBase Database uses the data type with the highest precedence, explicitly converts parameters of other types to this data type, and returns the value of this data type.
For a simple case expression or searched case expression, the data types of all return_exprs expressions must be the same (for example, CHAR, VARCHAR2 and NCHAR; NVARCHAR2, NUMBER and BINARY_FLOAT; BINARY_DOUBLE), or must be numeric types. If all return expressions are of the numeric type, OceanBase Database uses the data type with the highest precedence, explicitly converts parameters of other types to this data type, and returns the value of this data type.
Examples
Example of a simple case expression
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;
Example of a searched case expression
SELECT AVG(CASE WHEN e.salary > 6000 THEN e.salary
ELSE 6000 END) "Average Salary" FROM employees e;