CASE WHEN - MaxCompute - Alibaba Cloud Documentation Center

Returns the value of result based on the calculation result of value or _condition.

Syntax

MaxCompute provides the following CASE WHEN syntax:

case <value>
when <value1> then <result1>
[
  when <value2> then <result2>
  when <valuen> then <resultn>
]
[
	else <resultm>
]
end

case
	when (<_condition1>) then <result1>
[
  when (<_condition1>) then <result2>
  when (<_condition1>) then <resultn>
]
[
	else <resultm>
]
end

Parameters

The decision conditions are executed from top to bottom. The other conditions are not executed if the previous condition is met.

value: required. The value that is used for comparison.
_condition: required. The condition that is used for comparison.
result: required. The return value.

Return value

If the data types of all result values are only BIGINT and DOUBLE, the values are returned after their data types are converted into the DOUBLE type.
If result values of the STRING type exist, the values are converted into the STRING type before they are returned. If a data type conversion is not supported, an error is returned. For example, data of the BOOLEAN type cannot be converted into the STRING type.
Conversions between other data types are not allowed.

Examples

Return the value of result based on the data in the sale_detail table that contains the shop_name (STRING), customer_id (STRING), and total_price (DOUBLE) columns. Data in the table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Sample statements:

select 
case  
when region='china' then 'default_region'
when region like 'shang%' then 'sh_region'
end as region 
from sale_detail;

The following result is returned:

+------------+
| region     |
+------------+
| default_region |
| default_region |
| default_region |
| sh_region  |
| sh_region  |
| sh_region  |
+------------+

Create a table and insert data into the table.

-- Create a Transaction Table 2.0 table, insert data into the table, and then query data from the table.
create table mf_casewhen (id bigint,name string);
-- Insert data into the table.
insert into table mf_casewhen 
     values(1,"a1"),
           (2,"a2"),
                 (3,"a3"),
                 (4,"a4"),
                 (5,"a5"),
                 (6,"a6"),
                 (7,"a7"),
                 (8,"a8"),
                 (9,"a9");

Format 1:

select
case
when id<2 then 't1'
when id<4 then 't2'
else 't3'
end as id_t
from mf_casewhen;

Sample return value:

+------+
| id_t |
+------+
| t1   |
| t2   |
| t2   |
| t3   |
| t3   |
| t3   |
| t3   |
| t3   |
| t3   |
+------+

Format 2:

select
case id
when 1 then 't1'
when 2 then 't2'
else 't3'
end as id_t
from mf_casewhen;

Sample return value:

+------+
| id_t |
+------+
| t1   |
| t2   |
| t3   |
| t3   |
| t3   |
| t3   |
| t3   |
| t3   |
| t3   |
+------+

Related functions

For more information, see Other functions.