is there any function in Hive similiar to decode in Oracle?

Question

I'm looking for a string function that works like Oracle's DECODE Having table tab with a single column col

col
----
a
b
c
d

With a simple query:

select decode(col,'a',1,'b',2',9) dec from tab

I'd expect result like this:

dec
---
1
2
9
9

I haven't found any build-in function in Language Manual. Is there any UDF that can simulate DECODE?

I don't want to use case clause.

Regards
Pawel

Answer 1

You could write a nested if statement.

Query:

select col
  , if(col='a', 1, if(col='b', 2, 9)) dec
from table

Output:

---------
col | dec
---------
 a     1
 b     2
 c     9
 d     9

Answer 2

It may be easier to read and to inspect if you avoid nesting by using a flat CASE WHEN THEN expression :

SELECT
  CASE 
    WHEN col = 'a' THEN 1
    WHEN col = 'b' THEN 2
    ELSE 9
  END dec
FROM tab