Use CASE WHEN with syntax and examples - MaxCompute

CASE WHEN is a conditional expression, similar to if/else statements in other programming languages. It evaluates conditions from top to bottom and returns the result of the first matching condition.

Syntax

MaxCompute supports two forms of CASE WHEN:

Searched form — evaluates a condition in each WHEN clause:

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

Simple form — compares a single value against each WHEN clause:

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

Parameters

Parameter	Required	Description
`value`	Yes	The value used for comparison (simple form).
`_condition`	Yes	The condition used for comparison (searched form).
`result`	Yes	The return value.

Conditions are evaluated from top to bottom. Once a condition matches, the remaining conditions are not evaluated.

Return value

The return type depends on the data types of all result values:

If all result values are BIGINT or DOUBLE, they are returned as DOUBLE.
If one or more result values are STRING:
- Result values that can be converted to STRING are returned as STRING.
- If any result value cannot be converted to STRING, an error is returned.

BOOLEAN cannot be converted to STRING. A CASE WHEN expression that mixes BOOLEAN and STRING result values returns an error.

Conversions between other data types are not allowed.

Examples

Filter rows by region

The following example queries the sale_detail table, which has columns shop_name (STRING), customer_id (STRING), total_price (DOUBLE), sale_date, and region. Sample data:

+------------+-------------+-------------+------------+------------+
| 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   |
+------------+-------------+-------------+------------+------------+

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

Result:

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

Compare both forms on the same table

Create a table and insert sample data:

-- 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");

Searched form — each WHEN clause specifies a range condition:

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

Result:

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

Simple form — each WHEN clause matches an exact value:

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

Result:

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

Related functions

For more information, see Other functions.