Sub-Queries with CASE structure in Oracle SELECT statements - Oracle

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.