Impala Conditional Functions
Impala supports the following conditional functions for testing equality, comparison operators, and nullity:
 CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END

Purpose: Compares an expression to one or more possible values, and returns a corresponding result when a match is found.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
 CASE WHEN a THEN b [WHEN c THEN d]... [ELSE e] END

Purpose: Tests whether any of a sequence of expressions is true, and returns a corresponding result for the first true expression.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
 coalesce(type v1, type v2, ...)

Purpose: Returns the first specified argument that is not NULL, or NULL if all arguments are NULL.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
 decode(type expression, type search1, type result1 [, type search2, type result2 ...] [, type default] )

Purpose: Compares an expression to one or more possible values, and returns a corresponding result when a match is found.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
Usage notes:
Can be used as shorthand for a CASE expression.
The original expression and the search expressions must of the same type or convertible types. The result expression can be a different type, but all result expressions must be of the same type.
Returns a successful match If the original expression is NULL and a search expression is also NULL. the
Returns NULL if the final default value is omitted and none of the search expressions match the original expression.
Examples:
The following example translates numeric day values into descriptive names:
SELECT event, decode(day_of_week, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday", "Unknown day") FROM calendar;
 if(boolean condition, type ifTrue, type ifFalseOrNull)

Purpose: Tests an expression and returns a corresponding result depending on whether the result is true, false, or NULL.
Return type: same as the ifTrue argument value
 ifnull(type a, type ifNotNull)

Purpose: Alias for the isnull() function, with the same behavior.
To simplify porting SQL with vendor extensions to Impala.
Added in: Impala 1.3.0
 isnull(type a, type ifNotNull)

Purpose: Tests if an expression is NULL, and returns the expression result value if not. If the first argument is NULL, returns the second argument.
Compatibility notes: Equivalent to the nvl() function from Oracle Database or ifnull() from MySQL. The nvl() and ifnull() functions are also available in Impala.
Return type: same as the first argument value
 nullif(expr1,expr2)

Purpose: Returns NULL if the two specified arguments are equal.
If the specified arguments are not equal, returns the value of expr1.
The data types of the expressions must be compatible, according to the conversion rules
from Data Types.
You cannot use an expression that evaluates to NULL for expr1;
that way, you can distinguish a return value of NULL from an argument
value of NULL, which would never match expr2.
Usage notes: This function is effectively shorthand for a CASE expression of the form:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
It is commonly used in division expressions, to produce a NULL result instead of a dividebyzero error when the divisor is equal to zero:
select 1.0 / nullif(c1,0) as reciprocal from t1;
You might also use it for compatibility with other database systems that support the same NULLIF() function.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
Added in: Impala 1.3.0
 nullifzero(numeric_expr)

Purpose: Returns NULL if the numeric expression evaluates to 0,
otherwise returns the result of the expression.
Usage notes: Used to avoid error conditions such as dividebyzero in numeric calculations. Serves as shorthand for a more elaborate CASE expression, to simplify porting SQL with vendor extensions to Impala.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
Added in: Impala 1.3.0
 nvl(type a, type ifNotNull)

Purpose: Alias for the isnull() function. Tests if an expression is NULL, and returns the expression result value if not. If the first argument is NULL, returns the second argument. Equivalent to the nvl() function from Oracle Database or ifnull() from MySQL.
Return type: same as the first argument value
Added in: Impala 1.1
 zeroifnull(numeric_expr)

Purpose: Returns 0 if the numeric expression evaluates to NULL,
otherwise returns the result of the expression.
Usage notes: Used to avoid unexpected results due to unexpected propagation of NULL values in numeric calculations. Serves as shorthand for a more elaborate CASE expression, to simplify porting SQL with vendor extensions to Impala.
Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floatingpoint values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column
Added in: Impala 1.3.0
<< Impala Date and Time Functions  Impala String Functions >>  
Terms and Conditions Privacy Policy 