This topic describes conditional expression functions in Data Lake Analytics (DLA).
Simple CASE expression
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
Description: The simple CASE expression sequentially compares
expression
withvalue
in the WHEN clauses until it finds a match. If a match is found,result
of the corresponding THEN clause is returned. Otherwise,result
of theELSE
clause is returned.Example: Change the value
10
specified byage
to50
and add10
to other values specified byage
in thehello_mysql_vpc_rds.person
table.select * from hello_mysql_vpc_rds.person +------+------+----+ | id | name | age | +-----+------+-----+ | 1| james| 10| | 2| bond | 20| | 3| jack | 30| | 4| lucy | 40| +------+------+----+ select *, case age when 10 then 50 else(age+10) end as new_age from hello_mysql_vpc_rds.person +-----+------+-----+-----+ | id | name | age |new_age +-----+------+-----+-----+ | 1| james| 10| 50 | | 2| bond | 20|30 | | 3| jack | 30|40 | | 4| lucy | 40|50 |
Advanced CASE expression
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
Description: The advanced CASE expression sequentially calculates
condition
and returnsresult
for the firstcondition
that isTRUE
. If nocondition
isTRUE
,result
of theELSE
clause is returned.Example: Change the value
10
specified byage
to20
, add20
to the values ofage
for the records whosename
isbond
, and add30
to the values ofage
for other records in thehello_mysql_vpc_rds.person
table.select * from hello_mysql_vpc_rds.person +------+------+----+ | id | name | age | +-----+------+-----+ | 1| james| 10| | 2| bond | 20| | 3| jack | 30| | 4| lucy | 40| +------+------+----+ select *, case when age=10 then (age+10) when name='bond' then (age+20) else (age+30) end from hello_mysql_vpc_rds.person +-----+------+-----+-----+ | id | name | age |new_age +-----+------+-----+-----+ | 1| james| 10| 20 | | 2| bond | 20| 40 | | 3| jack | 30| 60 | | 4| lucy | 40| 70 |
IF
IF(condition,true_value)
Description: If the
condition
istrue
,true_value
is returned. Otherwise,null
is returned.Example:
select if((2+3)>4,5); +-------+ | _col0 | +-------+ | 5 |
IF(condition,true_value,false_value)
Description: If the
condition
istrue
,true_value
is returned. Otherwise,false_value
is returned.Example:
select if((2+3)<5,5,6); +-------+ | _col0 | +-------+ | 6 |
IFNULL
IFNULL(expr1,expr2)
Description: If
expr1
is notnull
,expr1
is returned. Otherwise,expr2
is returned.Example:
select ifnull(NULL,2); +-------+ | _col0 | +-------+ | 2 | select ifnull(1,0); +-------+ | _col0 | +-------+ | 1 |
NULLIF
NULLIF(expr1,expr2)
Description: If
expr1
is equal toexpr2
,NULL
is returned. Otherwise,expr1
is returned.Example:
select nullif(2,1); +-------+ | _col0 | +-------+ | 2 | select nullif(2,2); +-------+ | _col0 | +-------+ | NULL |
COALESCE
COALESCE(value[, …])
Description: This function sequentially checks
values
and returns the firstnon-null
value
.Example:
select coalesce(null,2,3); +--------------------+ | coalesce(null,2,3) | +--------------------+ | 2 |
TRY
TRY(expression)
Description: This function calculates and returns the value of
expression
. If an error occurs,null
is returned.When you query data, you can use this function to avoid exceptions in the query.
By default,
null
is returned if an exception occurs when you run this function. You can use the COALESCE() function to specify the return value of the TRY() function if an exception occurs.The TRY() function can be used to handle the following errors:
The divisor in an expression is 0.
The conversion is invalid or function parameters are invalid.
The value is out of the specified range.
Example:
select try(3/0); +----------+ | try(3/0) | +----------+ | NULL |
select try(3/2); +----------+ | try(3/2) | +----------+ | 1.5 |
The source data contains invalid data.
select * from shipping; --------------------------------------------------- origin_state | origin_zip | packages | total_cost --------------+------------+----------+------------ California | 94131 | 25 | 100 California | P332a | 5 | 72 California | 94025 | 0 | 155 New Jersey | 08544 | 225 | 490
A query fails if you do not run the TRY() function in the query.
select cast(origin_zip as BIGINT) from shipping; --------------------------------------------------- Query failed: Can not cast 'P332a' to BIGINT
No error is reported if you run the TRY() function in a query.
select try(cast(origin_zip as BIGINT)) from shipping; ------------ origin_zip ------------ 94131 NULL 94025 08544
When you run the COALESCE() function nested with the TRY() function, a specified value is returned if an error is reported in a query.
select coalesce(try(total_cost/packages), 0) as per_package from shipping; ------------- per_package ------------- 4 14 0 19
GREATEST
GREATEST(value1,value2,...)
Description: This function returns the maximum value of all input values.
Example:
select greatest(2,0); +---------------+ | greatest(2,0) | +---------------+ | 2 |
select greatest('B','A','C'); +-----------------------+ | greatest('B','A','C') | +-----------------------+ | C |
LEAST
LEAST(value1,value2,...)
Description: This function returns the minimum value of all input values.
Example:
select least('B','A','C'); +--------------------+ | least('B','A','C') | +--------------------+ | A |
select least(34.0,3.0,5.0,767.0); +---------------------------+ | least(34.0,3.0,5.0,767.0) | +---------------------------+ | 3.0 |
NVL2
NVL2(expr1,expr2,expr3)
Description: If the result of
expr1
is anon-null
value, the result ofexpr2
is returned. If the result ofexpr1
isnull
, the result ofexpr3
is returned.Example:
select nvl2(1, 2, 3); +---------------+ | nvl2(1, 2, 3) | +---------------+ | 2 |
select nvl2(null, 2, 3); +------------------+ | nvl2(null, 2, 3) | +------------------+ | 3 |
DECODE
DECODE(expr,search1,result1,search2,result2,...searchn,resultn,default)
Description: This function sequentially checks search results. If the result of
expr
is equal to that ofsearch1
,result1
is returned. If the result ofexpr
is equal to that ofsearch2
,result2
is returned, and so on. If noresult
is equal to that ofexpr
,default
is returned.Example:
select decode(1, 1, '1A', 2, '2A', '3A'); +-----------------------------------+ | decode(1, 1, '1A', 2, '2A', '3A') | +-----------------------------------+ | 1A |
select decode(1, 2, '1A', 1, '2A', '3A'); +-----------------------------------+ | decode(1, 2, '1A', 1, '2A', '3A') | +-----------------------------------+ | 2A |
select decode(1, 2, '1A', 2, '2A', '3A'); +-----------------------------------+ | decode(1, 2, '1A', 2, '2A', '3A') | +-----------------------------------+ | 3A |