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.

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

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

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 -

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 –