How to use DECODE in SQL?

SQL Basics tutorial on DECODE

In this SQL basics 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

 

Leave a Reply

Your email address will not be published. Required fields are marked *