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:

SELECT employee_id
       ,DECODE(employee_id,101,'A'
       ,102,'B'
       ,103,'C'
       ,104,'D'
       ,'F') Decoded
FROM employee;

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

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

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


Tagged , , , , , , , , , , , . Bookmark the permalink.