DECODE Function in Oracle SQL – PLSQL

The DECODE function in Oracle SQL / PLSQL has the functionality of IF-THEN-ELSE statement.

The Syntax for the DECODE function in Oracle SQL / PLSQL is:

SELECT DECODE(expression,search,result,[search, result]…[default])
FROM table_name;

  • ‘expression’ is the value to be compared
  • ‘search’ is the value to be compared with expression
  • ‘result’ is the value which is returned if the expression matches search
  • ‘default’ is option and is returned if search in not equal to expression. If default is omitted then DECODE will return NULL.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Decoded
101 A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Example 1:

Using DECODE in SELECT Statement

Suppose we write our query as:
[sourcecode language=”sql”]
SELECT employee_id
,DECODE(employee_id,101,’A’
,102,’B’
,103,’C’
,104,’D’
,’F’) Decoded
FROM employee;[/sourcecode]

We will get the following output:

Employee_Id Decoded
101 A
102 B
103 C
104 D
105 F

The above query is similar to writing
[sourcecode language=”sql”]
IF employee_id =101 THEN
Result = A
ELSE IF employee_id = 102 THEN
Result = B
ELSE IF employee_id = 104 THEN
Result = C
ELSE IF employee_id = 104 THEN
Result = D
ELSE
Result = F
[/sourcecode]

Here we can see that we have used DECODE function as an IN THEN ELSE loop.


Leave a Reply

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