:Control flow functions

Sample data

In this example, a table named conditiontest is used to test the control flow functions.

Execute the following statements to create a table named conditiontest and insert data into the table:

CREATE TABLE conditiontest(a INT) DISTRIBUTED BY HASH(a);
INSERT INTO conditiontest VALUES (1),(2),(3);

CASE

Syntax 1

CASE expression
    WHEN value THEN result
    [WHEN value THEN result ...]
    [ELSE result]
    END   

• Description: This function compares the values of the value parameter with the value of the expression parameter in sequence. If the value of the value parameter is the same as the value of the expression parameter, this function returns the result value of the value parameter. If no value of the value parameter is the same as the value of the expression parameter, this function returns the result value in the ELSE clause.

• Example:

  SELECT a,
        CASE a
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        ELSE 'three'
        END as caseresult
  FROM conditiontest;

  Sample result:

  +---+------------+
  | a | caseresult |
  +---+------------+
  | 2 | two        |
  | 1 | one        |
  | 3 | three      |
  +---+------------+

Syntax 2

CASE
    WHEN condition THEN result
    [WHEN condition THEN result...]
    [ELSE result]
    END

• Description: This function checks the values of the condition parameter in sequence. If the value of the condition parameter is true, this function returns the result value of the condition parameter. If all values of the condition parameter are false, this function returns the result value in the ELSE clause.

• Example:

  SELECT a,
        CASE
        WHEN a=1 THEN 'one1'
        WHEN a=2 THEN 'two2'
        ELSE 'three3'
        END as caseresult
  FROM conditiontest;

  Sample result:

  +---+------------+
  | a | caseresult |
  +---+------------+
  | 1 | one1       |
  | 3 | three3     |
  | 2 | two2     	 |
  +---+------------+

IF

Syntax 1

IF(condition, true_value)

• Description: If the value of the condition parameter is true, this function returns the value of the true_value parameter. Otherwise, this function returns null.

• Example:

  SELECT IF((2+3)>4,5);

  +-------+
  | _col0 |
  +-------+
  |     5 |

Syntax 2

IF(condition, true_value, false_value)

• Description: If the value of the condition parameter is true, this function returns the value of the true_value parameter. Otherwise, this function returns the value of the false_value parameter.

• Example:

  SELECT IF((2+3)<5,5,6);

  +-------+
  | _col0 |
  +-------+
  |     6 |

IFNULL

IFNULL(expr1,expr2)

• Description: If the value of the expr1 parameter is not null, this function returns the value of the expr1 parameter. Otherwise, this function returns the value of the expr2 parameter.

• Examples:

  Example 1:

  SELECT IFNULL(NULL,2);

  +-------+
  | _col0 |
  +-------+
  |     2 |
  +-------+

  Example 2:

  SELECT IFNULL(1,0);

  +-------+
  | _col0 |
  +-------+
  |     1 |
  +-------+

NULLIF

NULLIF(expr1,expr2)

• Description: If the value of the expr1 parameter is the same as the value of the expr2 parameter, this function returns null. Otherwise, this function returns the value of the expr1 parameter.

• Examples:

  Example 1:

  SELECT NULLIF (2,1);

  +-------+
  | _col0 |
  +-------+
  |     2 |
  +-------+

  Example 2:

  SELECT NULLIF (2,2);

  +-------+
  | _col0 |
  +-------+
  | NULL  |
  +-------+