One of the most powerful and overlooked SQL statements is the DECODE statement. The DECODE statement has the following syntax:
DECODE(val, exp1, exp2, exp3, exp4, ..., def);
DECODE will first evaluate the value or expression val and then compare expression exp1 to val. If val equals exp1, expression exp2 will be returned. If val does not equal exp1, expression exp3 will be evaluated and returns expression exp4 if val equals exp3. This process continues until all expressions have been evaluated. If there are no matches, the default def will be returned.
SELECT e.first_name,
e.last_name,
e.job_title,
DECODE(e.job_title, 'President', '******', e.salary)
FROM emp e
WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)
FROM emp z);
In this example, all manager names will be retrieved with their salaries. When the row identifying the president is displayed, show '******' instead of his salary. Also notice the NVL function used to evaluate a null manager ID. Only the president will have a null manager ID, which would not have been retrieved without the NVL.
Also notice that DECODE is evaluating job_title and returning salary, which would normally be a data type mismatch since the job title and salary columns are different data types but is okay here.
SELECT e.first_name,
e.last_name,
e.job_title,
e.salary
FROM emp e
WHERE DECODE(USER,'PRES',e.emp_id,
UPPER(e.last_name),e.emp_id, 0) = e.emp_id ;
In this example, if the user is the president, all employees will be returned with their corresponding salary. For all other users, only one row will be retrieved, enabling the user to see his or her own salary only.
SELECT e.first_name,
e.last_name,
e.job_title,
DECODE(USER,'ADMIN',DECODE(e.job_title, 'PRESEDENT', '*****', e.salary),
'PRES', e.salary, '******')
FROM emp e
WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)
FROM emp z);
In this example, the DECODE statement is nested with another DECODE statement. If the Oracle user is 'ADMIN', show the salaries except for the president's salary. If the Oracle user is 'PRES', show all salaries and if the user is anybody else, return '******'.
Another place the DECODE statement can be used is in the ORDER BY clause. The next example will sort the output in such a way that the president is the first row returned followed by the departments 'SALES', 'ADMIN', and then 'IS' with their corresponding employees.
SELECT d.name,
e.job_title,
e.first_name,
e.last_name
FROM emp e,
dept d
WHERE d.dept_no = e.dept_no
ORDER BY DECODE(e.job_title,'PRESIDENT', 0,
DECODE(d.name,'SALES', 1,
'ADMIN', 2, 3)), e.last_name;
This example does not ORDER BY e.job_title but uses this column to search for the title 'PRESIDENT' and returns a 0. For all other rows, another DECODE is used to evaluate the department name and returning numbers 1, 2, or 3 depending upon what the department name is. After the DECODEs are finished, the data is further sorted by employee last name e.last_name.
DECODE(val, exp1, exp2, exp3, exp4, ..., def);
DECODE will first evaluate the value or expression val and then compare expression exp1 to val. If val equals exp1, expression exp2 will be returned. If val does not equal exp1, expression exp3 will be evaluated and returns expression exp4 if val equals exp3. This process continues until all expressions have been evaluated. If there are no matches, the default def will be returned.
SELECT e.first_name,
e.last_name,
e.job_title,
DECODE(e.job_title, 'President', '******', e.salary)
FROM emp e
WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)
FROM emp z);
In this example, all manager names will be retrieved with their salaries. When the row identifying the president is displayed, show '******' instead of his salary. Also notice the NVL function used to evaluate a null manager ID. Only the president will have a null manager ID, which would not have been retrieved without the NVL.
Also notice that DECODE is evaluating job_title and returning salary, which would normally be a data type mismatch since the job title and salary columns are different data types but is okay here.
SELECT e.first_name,
e.last_name,
e.job_title,
e.salary
FROM emp e
WHERE DECODE(USER,'PRES',e.emp_id,
UPPER(e.last_name),e.emp_id, 0) = e.emp_id ;
In this example, if the user is the president, all employees will be returned with their corresponding salary. For all other users, only one row will be retrieved, enabling the user to see his or her own salary only.
SELECT e.first_name,
e.last_name,
e.job_title,
DECODE(USER,'ADMIN',DECODE(e.job_title, 'PRESEDENT', '*****', e.salary),
'PRES', e.salary, '******')
FROM emp e
WHERE e.emp_id in (SELECT NVL(z.manager_id, e.emp_id)
FROM emp z);
In this example, the DECODE statement is nested with another DECODE statement. If the Oracle user is 'ADMIN', show the salaries except for the president's salary. If the Oracle user is 'PRES', show all salaries and if the user is anybody else, return '******'.
Another place the DECODE statement can be used is in the ORDER BY clause. The next example will sort the output in such a way that the president is the first row returned followed by the departments 'SALES', 'ADMIN', and then 'IS' with their corresponding employees.
SELECT d.name,
e.job_title,
e.first_name,
e.last_name
FROM emp e,
dept d
WHERE d.dept_no = e.dept_no
ORDER BY DECODE(e.job_title,'PRESIDENT', 0,
DECODE(d.name,'SALES', 1,
'ADMIN', 2, 3)), e.last_name;
This example does not ORDER BY e.job_title but uses this column to search for the title 'PRESIDENT' and returns a 0. For all other rows, another DECODE is used to evaluate the department name and returning numbers 1, 2, or 3 depending upon what the department name is. After the DECODEs are finished, the data is further sorted by employee last name e.last_name.
