CASE Expressions in Apache Impala

The CASE function assesses a set of criteria and returns one of many result expressions. The CASE expression’s WHEN and THEN clauses are similar to most programming languages’ IF THEN expressions. The WHEN statement defines the condition to be checked, and the THEN statement describes the action to be taken if the WHEN condition returns TRUE.

The CASE expression is divided into two types: simple and searched.

The simple CASE statement compares an expression to a set of simple expressions, whereas the searched CASE expression examines a set of Boolean expressions. The ELSE argument is available in both forms.

Syntax:

–Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ …n ]
[ ELSE else_result_expression ]
END

–Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ …n ]
[ ELSE else_result_expression ]
END

Examples:
–Simple CASE expression:
SELECT
   ename,
   job,
   CASE deptno
             WHEN 10 THEN ‘ACCOUNTING’
             WHEN 20 THEN ‘RESEARCH’
             WHEN 30 THEN ‘SALES’
             WHEN 40 THEN ‘OPERATIONS’
        ELSE ‘No Department’
    END
FROM emp;



–Searched CASE expression:
SELECT
   ename,
   job,
   CASE WHEN sal <= 1500 THEN sal*0.5
          WHEN sal > 1500 AND sal <= 2000 THEN sal*0.3
          WHEN sal > 2000 AND sal <= 10000 THEN sal*0.4
      ELSE sal
   END
FROM emp;



Hope you find this article helpful.

Please subscribe for more interesting updates.