SQL Basics tutorial on DECODE
In this SQL tutorial on DECODE, we are going to learn about Decode command in SQL (only applicable for Oracle). Every database system has some special and useful SQL commands. Oracle has an extremely powerful command known as DECODE. Please note that, using DECODE command is only possible in Oracle.
Implementing DECODE
How do you implement IF-THEN-ELSE in an SQL query in ORACLE? Let’s take an example to understand the problem first?
You have a table named “Employees”. The “Employees” table has the following fields:
- Emp ID
- Emp Name
- Designation
- Address
- Contact No 1
- Contact No 2
- Basicsal
The company decided to pay the bonus as a multiple of base salary. This will be calculated as follows:
If Designation = “Project Manager” then
Bonus = 2.3 X Basic Salary
Else if Designation = “Program Manager” then
Bonus = 3.6 X Basic Salary
Else
Bonus = 1.2 X Basic Salary
End If
If you are going to use a programming language, you can use If-Else-Then statements to code but SQL queries don’t support these so How do you fetch the data using a single SQL query but still using the above conditions?
The solution is to use DECODE, here is the query?
SELECT EmpID, EmpName,
DECODE (Designation, ‘Project Manager’, 2.3 X Basic Salary, ‘Program Manager’, 3.6 X Basic Salary, 1.2 X Basic Salary)
FROM Employees;
Alternative to DECODE
Is there any other way to achieve the same result in ORACLE? Of course, yes, using CASE.
Using CASE:
SELECT EmpID, EmpName,
CASE Designation,
WHEN ‘Project Manager’ THEN 2.3 X Basic Salary
WHEN ‘Program Manager’ THEN 3.6 X Basic Salary
ELSE 1.2 X Basic Salary
FROM Employees;
This article was in series of articles, we have been publishing under “SQL tutorial for beginners”. Here are links to a 3-part SQL tutorial series:
SQL tutorial for Beginners Part 1
SQL tutorial for Beginners Part 2
SQL tutorial for Beginners Part 3
Cheers
