Inserting Sub-Queries in SELECT Statements in Oracle - Sub-Queries with CASE structure in Oracle SELECT statements
(Page 5 of 6 )
Now let us go through an interesting topic on CASE structure. Let us see how a CASE structure works. Consider the following statement:
SELECT
empno,
ename,
CASE job
WHEN 'SALESMAN' THEN 'SALES'
WHEN 'MANAGER' THEN 'MGMT'
ELSE job
END AS jobfunction
sal
FROM emp
When the above query is executed, it returns four columns (empno, ename, jobfunction, sal). The only eye-catching issue from the above is the following structure:
CASE job
WHEN 'SALESMAN' THEN 'SALES'
WHEN 'MANAGER' THEN 'MGMT'
ELSE job
END AS jobfunction
The above dictates something very similar to the following:
- If the value of “job” is “salesman” return “sales.”
- If the above condition fails and if the value of “job” is “manager” return “mgmt.”
- If both of the above conditions fail then return the same value of “job.”
- All the values must be returned in a new column with the heading “jobfunction.”
You need to observe that I specified the column (job) along with CASE. The conditions of WHEN work with the values available within that column. We can even work with different relational (and SQL) operators within the WHEN condition as shown in the following example:
SELECT
empno,
ename,
CASE
WHEN comm IS NULL OR comm=0 THEN '-NA-'
ELSE TO_CHAR(comm)
END AS comm,
sal
FROM emp
In the above case, the conditions are directly used within the WHEN statement and you need not specify any column with the CASE.
Finally, you can even work with sub-queries within the CASE structure as follows:
SELECT
empno,
ename,
CASE
WHEN sal >= (SELECT avg(sal) FROM emp) THEN 'HIGH'
ELSE 'LOW'
END AS pay,
sal
FROM emp
The above returns a new column named “pay,” which contains either “HIGH” or “LOW” based on their salary compared to the average salary of all employees.
Next: Sub-Queries as (or part of) columns in Oracle SELECT statements >>
More Oracle Articles
More By Jagadish Chatarji
/ 36 
