Conditional Function
Last Updated：2021-04-13
Palo supports the following conditional functions:
1.case
2.coalesce
3.if
4.ifnull
5.nullif
CASE
Description
CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END- Function: compare the expression with several possible values and return the corresponding results when matching
- Return type: type of result returned after matching
Example
mysql> select case tiny_column when 1 then "tiny_column=1" when 2 then "tiny_column=2" end from small_table limit 2;
+-------------------------------------------------------------------------------+
| CASE`tiny_column` WHEN 1 THEN 'tiny_column=1' WHEN 2 THEN 'tiny_column=2' END |
+-------------------------------------------------------------------------------+
| tiny_column=1 |
| tiny_column=2 |
+-------------------------------------------------------------------------------+
mysql> select case when tiny_column = 1 then "tiny_column=1" when tiny_column = 2 then "tiny_column=2" end from small_table limit 2;
+-------------------------------------------------------------------------------+
| CASE`tiny_column` WHEN 1 THEN 'tiny_column=1' WHEN 2 THEN 'tiny_column=2' END |
+-------------------------------------------------------------------------------+
| tiny_column=1 |
| tiny_column=2 |
+-------------------------------------------------------------------------------+Keywords
case COALESCE
Description
coalesce( expression,value1,value2……,valuen)- Function: return the first non-empty expression in all parameters including expression. The expression is an expression to be detected, and the number of parameters after it is variable.
- Return type: type of result returned after matching
Example
mysql> select coalesce(NULL, '1111', '0000');
+--------------------------------+
| coalesce(NULL, '1111', '0000') |
+--------------------------------+
| 1111 |
+--------------------------------+Keywords
coalesceIF
Description
if(boolean condition, type ifTrue, type ifFalseOrNull)- Function: test an expression and return the corresponding result according to whether the result is true or false
- Return type: type of ifTrue expression result
Example
mysql> select if(tiny_column = 1, "true", "false") from small_table limit 1;
+----------------------------------------+
| if(`tiny_column` = 1, 'true', 'false') |
+----------------------------------------+
| true |
+----------------------------------------+Keywords
ifIFNULL
Description
ifnull(expr1, expr2)- Function: test an expression and return the second parameter if the expression is NULL, otherwise return the first parameter.
- Return type: type of the first parameter
Example
mysql> select ifnull(1,0);
+--------------+
| ifnull(1, 0) |
+--------------+
| 1 |
+--------------+
mysql> select ifnull(null,10);
+------------------+
| ifnull(NULL, 10) |
+------------------+
| 10 |
+------------------+Keywords
ifnullNULLIF
Description
nullif(expr1, expr2)- Function: If the two parameters are equal, then it will return NULL. Otherwise, return the value of the first parameter. It has the same effect as the following CASE WHEN.
- Return type: expr1 type or NULL
CASE
WHEN expr1 = expr2 THEN NULL
ELSE expr1
ENDExample
mysql> select nullif(1,1);
+--------------+
| nullif(1, 1) |
+--------------+
| NULL |
+--------------+
mysql> select nullif(1,0);
+--------------+
| nullif(1, 0) |
+--------------+
| 1 |
+--------------+Keywords
nullif