All Products
Search
Document Center

Case expressions

Last Updated: Jun 18, 2021

A case expression allows you to use the IF ... THEN ... ELSE logic in SQL statements without calling a stored procedure.

Syntax

CASE { simple_case_expression
     | searched_case_expression
     }
     [ ELSE else_expr ]
     END

The following code provides the syntax of simple_case_expression.

expr
  { WHEN comparison_expr THEN return_expr }...

The following code provides the syntax for searched_case_expression:

{ WHEN condition THEN return_expr }...

Usage rules

Validation of conditions

For a simple case expression, ApsaraDB for OceanBase uses expr as a base and searches WHEN ... THEN for the first comparison_expr that is equal to expr. Then, ApsaraDB for OceanBase returns the corresponding return_expr. If no WHEN ... THEN meets the condition and an ELSE clause exists, ApsaraDB for OceanBase returns else_expr. Otherwise, ApsaraDB for OceanBase returns NULL.

In a searched case expression, ApsaraDB for OceanBase searches from left to right until the condition is met. Then, ApsaraDB for OceanBase returns return_expr. If no condition is met and an ELSE clause exists, the database returns else_expr. Otherwise, the database returns NULL.

Calculation of conditions

ApsaraDB for OceanBase uses the short-circuit calculation rule. For a simple case expression, the database calculates a comparison_expr value only before the database compares the value with expr. The database does not calculate all the comparison_expr values before the database compares a comparison_expr value with expr. Therefore, if the previous comparison_expr is equal to expr, ApsaraDB for OceanBase does not calculate the next comparison_expr value. For a searched case expression, the database performs serial computing for each condition to determine whether the condition is true. If the previous condition is true, ApsaraDB for OceanBase does not calculate the next condition.

Data types

For a simple case expression, the expr and all the comparison_expr values must have the same data type, such as CHAR, VARCHAR2, NCHAR, or NVARCHAR2, and NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. Alternatively, they must all have the numeric type. If the data type of all the returned expressions is the numeric type, ApsaraDB for OceanBase selects the data type that has the highest precedence. ApsaraDB for OceanBase explicitly converts the other parameters to this data type and returns this data type.

For a simple case expression and a searched case expression, all the return_exprs must have the same data type, such as CHAR, VARCHAR2, NCHAR, or NVARCHAR2, and NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. Alternatively, they must all have the numeric type. If the data type of all the returned expressions is the numeric type, ApsaraDB for OceanBase selects the data type that has the highest precedence. ApsaraDB for OceanBase explicitly converts the other parameters to this data type and returns this data type.

Examples

SELECT cust_last_name,
    CASE credit_limit
    WHEN 100 THEN 'Low'
    WHEN 5000 THEN 'High'
    ELSE 'Medium' END AS credit
FROM customer
ORDER BY cust_last_name, credit;

The following example shows a searched case expression:

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
          ELSE 2000 END) "Average Salary" FROM employee e;