What is the usage of the decode function in DB2?

In DB2, the DECODE function is used to compare an expression with a series of possible values and return the result of the matching value. The syntax of the DECODE function is as follows:

DECODE(expression, search_value1, result1, search_value2, result2, ..., default_result)

In this case, expression is the expression to be compared, search_value1, search_value2, … are possible values, result1, result2, … are the corresponding results for each value, and default_result is the default result returned when there is no matching value. If expression matches search_value1, return result1, if it matches search_value2, return result2, and so on. If there is no matching value, return default_result.

For example, suppose there is a table called employees that contains the fields employee_id and department_id. We can use the DECODE function to return the department name for each employee.

SELECT employee_id, DECODE(department_id, 1, 'Sales', 2, 'Marketing', 3, 'Finance', 'Other') AS department_name
FROM employees;

The query above will return the corresponding department name based on the value of the department_id field, if there is no matching value for department_id, it will return “Other”.