Impala Conditional Functions: IF, CASE, COALESCE, DECODE, NVL, ZEROIFNULL

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:BigData
  • Reading time:4 mins read

Cloudera Impala supports the various Conditional functions. You can use these function for testing equality, comparison operators and check if value is null. Following are Impala Conditional Functions:

impala conditional functions

Impala IF Conditional Function

This is the one of best Impala Conditional Functions and is similar to the IF statements in other programming languages. Tests an expression and returns a corresponding result depending on whether the result is true, false or null.

Syntax:

if(boolean condition, type ifTrue, type ifFalseOrNull)

For example;

select if(1=1,'TRUE','FALSE') as IF_TEST;

Impala CASE Conditional Function

This function Tests whether any of a sequence of expressions is true, and returns a corresponding result for the first true expression. You can use OR, IN, REGEXP in the CASE expressions.

Syntax:

CASE WHEN a THEN b [WHEN c THEN d]... [ELSE e] END
Or
CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END

For example;

select case x when 1 then 'one' 
 when 2 then 'two' 
 when 0 then 'zero' 
 else 'out of range' 
 end
 from t1;

select case 
 when dayname(now()) in ('Saturday','Sunday') then 'result undefined on weekends' 
 when x > y then 'x greater than y' 
 when x = y then 'x and y are equal' 
 when x is null or y is null then 'one of the columns is null' 
 else null 
 end 
 from t1;

Impala COALESCE Conditional Function

Returns the first non-null value for list of values provided as arguments.

Syntax:

coalesce(value1, value 2, ...)

For example;

select coalesce(null,'a',null,'b');

Impala DECODE Conditional Function

Compares an expression to one or more possible values, and returns a corresponding result when a match is found.

Syntax:

decode(<expr>, <search1>,<result1>, …<search N>, <result N>, <default>)

For example;

SELECT event, decode(day_of_week, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 
 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday", "Unknown day") 
 FROM calendar;

Impala ISNULL and IFNULL Conditional Functions

This function will test if expression is null, it’ll return expression if result is not null otherwise second argument is returned.

Syntax:

ifnull(arg1, arg2)
or
isnull(arg1, arg2)

For example;

select ifnull(null,'yes');
select isnull(null,'yes');

Impala NULLIF Conditional Function

This function returns null if two specified arguments are equal.

Syntax:

nullif(expr1,expr2)

For example;

select nullif(1,1);

Impala NULLIFZERO Conditional Function

This function returns null if the expression is evaluates to 0, otherwise result of the expression is returned.

Syntax:

nullifzero(numeric_expr)

For example;

select nullifzero(0);

Impala ZEROIFNULL Conditional Function

This function returns zero if numeric expression evaluates to null, otherwise result of the expression is returned.

Syntax:

zeroifnull(numeric_expr)

For example;

select zeroifnull(null);

Impala NVL Conditional Function

This function will test if expression is null, it’ll return expression if result is not null otherwise second argument is returned.

Syntax:

NVL(arg1, arg2)

For example;

select nvl(null,'value is null');
Tags: , ,