All Products
Search
Document Center

Conditional expression functions

Last Updated: Mar 22, 2021

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 with value 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 the ELSE clause is returned.

  • Example: Change the value 10 specified by age to 50 and add 10 to other values specified by age in the hello_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 returns result for the first condition that is TRUE. If no condition is TRUE, result of the ELSE clause is returned.

  • Example: Change the value 10 specified by age to 20, add 20 to the values of age for the records whose name is bond, and add 30 to the values of age for other records in the hello_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 is true, 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 is true, 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 not null, 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 to expr2, 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 first non-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 a non-null value, the result of expr2 is returned. If the result of expr1 is null, the result of expr3 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 of search1, result1 is returned. If the result of expr is equal to that of search2, result2 is returned, and so on. If no result is equal to that of expr, 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                                |