HomeOracle Page 5 - Inserting Sub-Queries in SELECT Statements in Oracle
Sub-Queries with CASE structure in Oracle SELECT statements - Oracle
This is the second article in a series concentrating on working with sub-queries in Oracle. My previous article (first in this series) explained the “dual” table in detail and gave a few practical examples of sub-queries. In this article, I shall introduce sub-queries at various interesting places within the same SELECT statement.
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.