All Products
Search
Document Center

Conditional expressions

Last Updated: Jan 15, 2019

CASE

There are two forms of the standard SQL ‘CASE’ expressions. The “simple” form of the CASE expression searches each value from left to right until it finds one that meets the expression.

Syntax:

  1. CASE expression
  2. WHEN value THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

The result for the matching value is returned. If no match is found, the result from the ELSE clause is returned.

Example:

  1. SELECT a,
  2. CASE a
  3. WHEN 1 THEN 'one'
  4. WHEN 2 THEN 'two'
  5. ELSE 'many'
  6. END

The “advanced” form of the CASE expression calculates each condition from left to right until one is TRUE and returns the matching result.

Syntax:

  1. CASE
  2. WHEN condition THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

If no conditions are TRUE, the result from the ELSE clause is returned.

Example:

  1. SELECT a, b,
  2. CASE
  3. WHEN a = 1 THEN 'aaa'
  4. WHEN b = 2 THEN 'bbb'
  5. ELSE 'ccc'
  6. END

IF

The IF function language construct is the same as the following CASE expression:

  1. CASE
  2. WHEN condition THEN true_value
  3. [ ELSE false_value ]
  4. END

Input value: condition, true_valueReturned value: Evaluates and returns true_value if condition is TRUE. Otherwise, NULL is returned.

Input value: condition, true_value, false_valueReturned value: Returns true_value if condition is TRUE. Otherwise, evaluates, and returns false_value.

COALESCE

Input value: valueReturned value: The first non-null value.

Similar to CASE expression, only necessary arguments are evaluated.

IFNULL

If expr1 is a non-null value, expr1 is returned, otherwise expr2 is returned.

Syntax:

IFNULL(expr1,expr2)

Example:

  1. SELECT IFNULL(1,0);
  2. SELECT IFNULL(NULL,10);

NULLIF

Input value: value1, value2Returned value: Returns NULL if value1 equals value2. Otherwise, value1 is returned.

Syntax:

NULLIF(expr1,expr2)

Example:

  1. SELECT NULLIF(1,1);
  2. SELECT NULLIF(1,2);

TRY

Input value: expression

Evaluates an expression and returns NULL when an error occurs.

TRY function is applicable in cases where NULL and the default value is the preferable returned value for handling errors. To set default values, use the TRY function in conjunction with the COALESCE function.

TRY can handle the following errors:

  • Division by zero
  • Invalid cast or invalid function parameters
  • Numeric value out of range
Example

The source table contains invalid data:

  1. SELECT * FROM shipping;
  1. origin_state | origin_zip | packages | total_cost
  2. --------------+------------+----------+------------
  3. California | 94131 | 25 | 100
  4. California | P332a | 5 | 72
  5. California | 94025 | 0 | 155
  6. New Jersey | 08544 | 225 | 490
  7. (4 rows)

Query failure without TRY:

  1. SELECT CAST(origin_zip AS BIGINT) FROM shipping;
  1. Query failed: Cannot cast 'P332a' to BIGINT

NULL values with TRY:

  1. SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
  1. origin_zip
  2. ------------
  3. 94131
  4. NULL
  5. 94025
  6. 08544
  7. (4 rows)

Query failure without TRY:

  1. SELECT total_cost / packages AS per_package FROM shipping;
  1. Query failed: / by zero

Default values with COALESCE and TRY:

  1. SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;
  1. per_package
  2. -------------
  3. 4
  4. 14
  5. 0
  6. 19
  7. (4 rows)

Control Flow functions in MySQL supported by AnalyticDB

COALESCE

Returns the first non-null value in the list. If all values in the list are null, NULL is returned.

Syntax:

COALESCE(value,...)

Example:

  1. SELECT COALESCE(NULL,1);
  2. SELECT COALESCE(NULL,NULL,NULL);
GREATEST

Returns the greatest value for functions with two or more arguments.

Syntax:

GREATEST(value1,value2,...)

Example:

  1. SELECT GREATEST(2,0);
  2. SELECT GREATEST(34.0,3.0,5.0,767.0);
  3. SELECT GREATEST('B','A','C');
LEAST

Returns the smallest argument if the function has two or more arguments.

Syntax:

LEAST(value1,value2,...)

Example:

  1. SELECT LEAST(2,0);
  2. SELECT LEAST(34.0,3.0,5.0,767.0);
  3. SELECT LEAST('B','A','C');

Control Flow functions in Oracle supported by AnalyticDB

NVL2

Determines the value returned by the query depending on whether the specified expression is null or non-null.

 If expr1 is a non-null value, then NVL2 function returns expr2. If expr1 is NULL, then NVL2 function returns expr3.

Syntax:

NVL2(expr1, expr2, expr3)

Example:

  1. SELECT NVL2(1, 2, 3);
  2. SELECT NVL2(NULL, 2, 3);
DECODE

Compares the value of expression to search each value individually. If the expression is equal to the search value, the corresponding result is returned.

Syntax:

DECODE(expr, search, result, default)

Example:

  1. SELECT DECODE(1, 1, '1A', 2, '2A', '3A');
  2. SELECT DECODE(2, 1, '1A', 2, '2A', '3A');