Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Inserting Sub-Queries in SELECT Statements in Oracle
  2. Sub-Queries returning single and multiple values in Oracle
  3. Sub-Queries as part of the BETWEEN operator in Oracle
  4. Derived tables (or inline views) with Sub-Queries in Oracle
  5. Sub-Queries with CASE structure in Oracle SELECT statements
  6. Sub-Queries as (or part of) columns in Oracle SELECT statements
By: Jagadish Chatarji
Rating: starstarstarstarstar / 62
November 14, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: