Decode in Oracle SQL

                 Usage of  Decode in Oracle SQL

DECODE() is a function and proprietary to Oracle SQL

DECODE compares the expression to each search value one by one using IF-THEN-ELSE else logic

If expression=search result, then it returns the corresponding result. 

If expression!=search result (no match is found), then it returns default.

If default is omitted, then it returns null.

Syntax for using Decode:

select 

columns,

DECODE(expression, search_1, result_1, [search_2, result_2, ...,] [default]) as <new_column>

from <table_name>;

---------------------------------

Q: Display hierarchy using Decode.

SELECT ENAME, JOB

FROM EMP

ORDER BY DECODE(JOB, 'PRESIDENT', 1, 

                     'MANAGER',   2, 

                     'ANALYST',   3, 

                     'others',4);

Name JOB  

KING PRESIDENT

CLARK MANAGER

BLAKE MANAGER

JONES MANAGER

FORD ANALYST

SCOTT ANALYST

ALLEN SALESMAN

TURNER SALESMAN

ADAMS CLERK

JAMES CLERK

WARD SALESMAN

MILLER CLERK

MARTIN SALESMAN

SMITH CLERK  

 

OR

---------------------------------

SELECT ENAME, JOB,

       DECODE(JOB, 'PRESIDENT', 'Top Boss',

                   'MANAGER',   'Middle Mgmt',

                   'SALESMAN',  'Field Sales Staff',

                   'CLERK','Field Clerk Staff',

                   'ANALYST', 'Field ANALYST Staff',

                   'Other') AS Job_Category

FROM EMP

order by Job_Category desc;

---------------------------------

Result:

Name    JOB        Job_Category

KING PRESIDENT Top Boss

BLAKE MANAGER Middle Mgmt

CLARK MANAGER Middle Mgmt

JONES MANAGER Middle Mgmt

ALLEN SALESMAN Field Sales Staff

WARD SALESMAN Field Sales Staff

MARTIN SALESMAN Field Sales Staff

TURNER SALESMAN Field Sales Staff

MILLER CLERK Field Clerk Staff

SMITH CLERK Field Clerk Staff

JAMES CLERK Field Clerk Staff

ADAMS CLERK Field Clerk Staff

FORD ANALYST Field ANALYST Staff

SCOTT ANALYST Field ANALYST Staff

 ---------------------------------

select 

job,

Decode

(job,'PRESIDENT','CEO','MANAGER','TL','ANALYST','QA','CLERK','FIN','SALESMAN','MARKETTING','Others') job_codes from emp

order by  DECODE(JOB, 'PRESIDENT', 1, 

                     'MANAGER',   2, 

                     'ANALYST',   3, 

                     'others',4);

---------------------------------  

JOB JOB_CODES  

PRESIDENT CEO

MANAGER TL

MANAGER TL

MANAGER TL

ANALYST QA

ANALYST QA

SALESMAN MARKETTING

SALESMAN MARKETTING

CLERK FIN

CLERK FIN

SALESMAN MARKETTING

CLERK FIN

SALESMAN MARKETTING

CLERK FIN  

---------------------------------

Q: Write an Oracle SQL query to display the employee names, their jobs, and their current salaries. Additionally, calculate a 'New_Salary' column based on the following logic: if the job is 'CLERK', the salary increases by 10%; if the job is 'ANALYST', the salary increases by 20%; for all other jobs, the salary remains the same. Use the DECODE function to implement this logic

SELECT ENAME, JOB,SAL,

       DECODE(JOB, 'CLERK',    SAL * 1.1, 

                   'ANALYST',  SAL * 1.2, 

                   SAL) AS "New_Salary"

FROM EMP;

logic for %

Orignal sal: 100%(1.0 as dicimal)

Increase sal: 10% means 0.1

so, the total: 1.0+0.1=1.1

or

sal+(sal*0.10)

sal*(1+0.10)

sal*1.1

---------------------------------

Q:How to Handle NULLs with DECODE?

Standard SQL comparison (= NULL) always fails because NULL is "unknown or blank value" However, DECODE treats two NULLs as being equal.

SELECT ENAME, COMM,

       DECODE(COMM, NULL, 'No Commission', 'Has Commission') AS Status

FROM EMP;

ENAME COMM STATUS

SMITH No Commission

ALLEN 300 Has Commission

WARD 500 Has Commission

JONES No Commission

MARTIN 1400 Has Commission

BLAKE No Commission

CLARK No Commission

SCOTT No Commission

KING No Commission

TURNER 0 Has Commission

ADAMS No Commission

JAMES No Commission

FORD No Commission

MILLER No Commission

Note:

DECODE works with equality checks only.

It is very concise for simple mapping ( 1 -> 'Monday', 2 -> 'Tuesday').

If you need to check ranges (SAL > 2000), we must use a CASE statement instead.

Simple way of using decode function:

SELECT 

    DECODE(1, 1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thu', 5, 'Fri', 6, 'Sat', 7, 'Sun',8,null) AS week_day

FROM dual;