DECODE Function in Apache Impala

To compare each search value to the expression one by one, we can use the DECODE function that adds procedural if-then-else logic to the query. If expression is equal to a search, it returns the relevant result. The default value is used if no match is found. If no default is supplied, it returns null.

This function’s behaviour is same in other RDBMSs.

Let’s do some practice exercises.

Dataset-1
SwiftCode
ADCBAEAAATF
ABDIAEADAIN
AJMNAEAJXXX
ARABAEADABD

Refer to the above dataset, which only contains swift-codes. We can pass the relevant or readable value, which is the bank name, to the DECODE function. The SELECT statement is:

SELECT SwiftCode,
DECODE(SwiftCode, ‘ADCBAEAAATF’, ‘ABU DHABI COMMERCIAL BANK’,
‘ABDIAEADAIN’,’ABU DHABI ISLAMIC BANK’,
‘AJMNAEAJXXX’,’AJMAN BANK PJSC’,
‘ARABAEADABD’,’ARAB BANK PLC’)AS Bank
FROM BankCodes;

Dataset-2
Event,DayOfWeek
Arts & Culture,1
Pop-up Performances,1
Light Show,2
Harmony,3
Moroccon Folk Music,5
Business & Entrepreneurship,6
Sports,Fitness and Wellbeing,7

Instead of retrieving the day number, the requirement is to retrieve the event and the day name. As a result, the SELECT statement will be:

SELECT event,
DECODE(day_of_week, 1, “Monday”,
2, “Tuesday”,
3, “Wednesday”,
4, “Thursday”,
5, “Friday”,
6, “Saturday”,
7, “Sunday”,
“Unknown day”)As DayName
FROM Events;