All Products
Search
Document Center

Process control functions

Last Updated: Jun 18, 2021

CASE

Declaration

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
OR
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

Description

In the returned result of the first case, value= compare-value. The prerequisite of the returned result of the second case is that the first condition is true. If no matching result is found, the result after ELSE is returned. If the ELSE part does not exist, the return value is NULL.

Example

obclient> select CASE 'b' when 'a' then 1 when 'b' then 2 END;
+----------------------------------------------+
| CASE 'b' when 'a' then 1 when 'b' then 2 END |
+----------------------------------------------+
|                                            2 |
+----------------------------------------------+
1 row in set (0.01 sec)

obclient> select CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end;
+--------------------------------------------------------------------------+
| CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end |
+--------------------------------------------------------------------------+
| a                                                                        |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)

obclient> select case when 1>0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1>0 then 'true' else 'false' end |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

IF

Declaration

IF(expr1,expr2,expr3)

Description

If the value of expr1 is TRUE (expr1<>0 and expr1<>NULL), the return result is expr2. Otherwise, the return result is expr3.

The return result of IF() can be a numeric value or string value, depending on the content.

If only one of expr2 and expr3 is NULL, the result of IF() is a non-NULL expression.

Example

obclient> select if(5>6, 'T','F'), if (5>6, 1, 0), if(null, 'True', 'False'), if(0, 'True', 'False')\G
*************************** 1. row ***************************
         if(5>6, 'T','F'): F
           if (5>6, 1, 0): 0
if(null, 'True', 'False'): False
   if(0, 'True', 'False'): False
1 row in set (0.01 sec)

IFNULL

Declaration

IFNULL(expr1, expr2)

Description

Assume that expr1 is not NULL. The return value of IFNULL() is expr1. Otherwise, its return value is expr2. The return value of IFNULL() is a numeric value or a string, depending on the context.

The default return value type of IFNULL() is determined based on the following rules.

Expression

Return value

The return value of expr1 or expr2 is a string.

String

The return value of expr1 or expr2 is a floating-point value.

Floating point

The return value of expr1 or expr2 is an integer.

Integer

If both expr1 and expr2 are strings, and either of them is case-sensitive, the return result is case-sensitive.

Example

obclient> SELECT IFNULL('abc', null), IFNULL(NULL+1, NULL+2), IFNULL(1/0, 0/1);
+---------------------+------------------------+------------------+
| IFNULL('abc', null) | IFNULL(NULL+1, NULL+2) | IFNULL(1/0, 0/1) |
+---------------------+------------------------+------------------+
| abc                 |                   NULL |           0.0000 |
+---------------------+------------------------+------------------+
1 row in set (0.01 sec)

NULLIF

Declaration

NULLIF(expr1, expr2)

Description

If expr1 = expr2, the return value is NULL. Otherwise, the return value is expr1. It is the same as CASE WHEN

expr1 = expr2 THEN NULL ELSE expr1 END. Note that if the parameters are not equal, the value of the two calculations is expr1.

Example

obclient> SELECT NULLIF('ABC', 123), NULLIF('123',123), NULLIF(NULL, 'abc');
+--------------------+-------------------+---------------------+
| NULLIF('ABC', 123) | NULLIF('123',123) | NULLIF(NULL, 'abc') |
+--------------------+-------------------+---------------------+
| ABC                | NULL              | NULL                |
+--------------------+-------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

ORA_DECODE

Declaration

ORA_DECODE (condition, value 1, return value 1, value 2, return value 2, ... value n, return value n, default value)

Description

The functionality of ORA_DECODE() is the same as that of the DECODE() function in Oracle database.

Mechanism of this function:

IF condition = value 1
THEN RETURN (return value 1)
ELSIF condition = value 2
THEN RETURN (return value 2)
......
ELSIF condition = value n
THEN RETURN (return value n)
ELSE RETURN (default value)
END IF