Summary -

In this topic, we described about the DECODE Statement with detailed example.

DECODE statement is used to check multiple conditions and returns corresponding value to that condition in result set when the condition became true. DECODE statement works on ORACLE database only. while checking conditions first condition itself satisfied DECODE statement won't go for next conditions and returns first condition values in result set. If not a single condition is satisfied DECODE statement returns ELSE part value in result set. If DECODE statement does not have ELSE part then NULL values returns in result set.

Syntax -

DECODE(expression,searchvalue1,result1,searchvalue2,result2,….
DEFAULT VALUE)  

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting rows from existing table by using DECADE statement.

Requirement – Getting rows from employee_details by using DECADE statement. The query was as follows –

SELECT
DECADE (dept_id, 1000, 3000,
        2000, 4000, 
        ' ' ) dept_id, emp_name, 
FROM employee_details; 

By executing above query we can get output like as follows -

dept_id emp_name
3000 Employee1
4000 Employee2
3000 Employee3
4000 Employee4
3000 Employee5
3000 Employee6
3000 Employee7
4000 Employee8
4000 Employee9

Scenario – Getting rows from existing table by using DECADE statement with ORDER BY clause.

Requirement – Getting rows from employee_details by using DECADE statement with ORDER BY clause.Consider that we have date_of_hire column has VARCHAR datatype for this query. The query was as follows –

SELECT
DECODE ( date_of_hire, 2019-07-11, 'First hired Employees',
                   2019-09-09, 'Second hired Employees',
                   2019-10-08, 'Third hired Employees',
                 'Employees not hired') date_of_hire, emp_name,
FROM employee_details ; 

By executing above query we can get output like as follows –

date_of_hire emp_name
First hired Employees Employee1
First hired Employees Employee2
First hired Employees Employee3
Third hired Employees Employee4
First hired Employees Employee5
Third hired Employees Employee6
First hired Employees Employee7
Second hired Employees Employee8
Third hired Employees Employee9