Hive Conditional Functions


IF( boolean condition, True Value, False Value ) 

The IF condition evaluates the “boolean Condition” and if the “boolean Condition” is true, then it returns the “True Value”. Otherwise, it returns the False Value.

Example:
                      if( 10>20, 10,20)   // 20
       


CASE Statement

//Simple Case   


CASE  Selector
       WHEN Expr THEN res1
       WHEN Expr THEN res2
        .....
        .....
       WHEN  ExprTHEN resn
       ELSE result
END


In Simple case we have to specify  a Selector, Which determines which group of action to get executed.

The selector is a Value that determines , Which WHEN  clause should  be executed.

The selector is evaluated only once for the whole selection process.

Simple Case internally evaluates  the selector using the equality operator ,Directly upon the process  and Compares with the  Exprs.

All the results must be of same datatype. This is the value returned once a condition is found to be true.

Simple Case is not suitable  for multi Conditional Analysis and other than the Equality Operator.

Example: 

CASE  deptno
       WHEN 10  THEN  'TEN'
       WHEN 20 THEN 'TWENTY'
       ELSE 'NOT FOUND'
END

//Searched  Case 

CASE
       WHEN SeaarchCondition1 THEN res1
        WHEN SeaarchCondition2 THEN res2
        .....
        .....
      WHEN SeaarchConditionN THEN resN
       ELSE result
END


In searched case ,the Selector is not present,It has Series of search Condition, That are evaluated in  Order to Determine Which Group of Actions to take place.

A searched case stmt has search conditions that yield Boolean values TRUE,FALSE,NULL

When  A particular search Condition Evaluates to True, Then group of Stnts Associated with this conditions are Excuted.

Example:

CASE

       WHEN deptnp=10 THEN 'Ten'
       WHENdeptno=20 THEN 'Twenty'
       ELSE  'NOt Found'

END


COALESCE( val1,val2,.....)

The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.

Example: COALESCE(NULL,10,20,NULL)   //10


Comments