In ANSI SQL 1999, there are four types of CASE statements:
Simple
Searched
NULLIF
COALESCE
Previous to Oracle9i, simple case statements were already supported. In Oracle9i, support for the remaining types of case statements is provided.
Simple CASE Statements
Simple case statements are much like the decode statement. They can be used to search and then replace a given value within a given SQL Statement. Here is an example:
SELECT ename,
(CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'Unassigned'
END ) as Department
FROM emp;
ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN Unassigned
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
In this example, if the deptno column has a 10 in it, the SQL query will return the value accounting rather than the number 10. If the deptno is not 10, 20, 30, or 40, then the CASE statement will fall through to the ELSE clause, which will return unassigned. Note that with a simple CASE statement, no comparison operators can be used.
Searched CASE Statements
The searched case statement is the much more powerful cousin of the simple case statement. The searched case statement is like an if…then…else structure, and can be used to conditionally search and replace values using logical operators and multiple conditions. Let's look at an example:
SELECT ename, sal, deptno,
CASE
WHEN sal <= 500 then 0
WHEN sal > 500 and sal<1500 then 100
WHEN sal >= 1500 and sal < 2500 and deptno=10 then 200
WHEN sal > 1500 and sal < 2500 and deptno=20 then 500
WHEN sal >= 2500 then 300
ELSE 0
END "bonus"
FROM emp;
ENAME SAL DEPTNO bonus
---------- ---------- ---------- ----------
SMITH 800 20 100
ALLEN 1600 90 0
WARD 1250 30 100
JONES 2975 20 300
MARTIN 1250 30 100
BLAKE 2850 30 300
CLARK 2450 10 200
In this example, you are trying to determine how much of a bonus each employee is eligible for. The bonus amount is based on the salary of the employee, but notice that some conditions have been added based on what department number the employee is in. You can see that a searched case statement can have many different when clauses, and that you can apply many criteria in those clauses to get the answers you need.
NULLIF and COALESCE
To further comply with SQL 1999, the NULLIF and COALESCE statements have been added to Oracle9i. The NULLIF statement is very simple. It takes two arguments. If they are equivalent, then the result is a NULL. If they are not equivalent, then the first argument is returned by the function. Here is an example of a NULLIF statement:
SELECT ename, NULLIF (comm, 0) COMM FROM emp;
ENAME COMM
---------- ----------
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
In this example, if the comm column (which is the commision for an employee) has a 0 value, it will be returned as a NULL as shown in the sample output.
The coalesce statement is a bit like the Oracle NVL function. Given an unlimited number of arguments, it will return the first non-null value in those arguments. Here is an example:
SELECT ename, COALESCE(comm, 0) COMM FROM emp;
ENAME COMM
---------- ----------
SMITH 0
ALLEN 300
WARD 500
JONES 0
MARTIN 1400
BLAKE 0
CLARK 0
SCOTT 0
In this case, if the comm column is NULL, a 0 value will be returned. Note that with coalesce, there is no implicit type conversion of the arguments passed to it, so the following code would not work:
SELECT ename, COALESCE(comm, 'None') FROM emp;
The following code, however, would work:
SELECT ename, COALESCE(to_char(comm), 'None') COMM FROM emp;
ENAME COMM
---------- ----------
SMITH None
ALLEN 300
WARD 500
JONES None
MARTIN 1400
BLAKE None
CLARK None
SCOTT None
(3)SCALAR SUBQUERIES
A scalar subquery expression is a subquery that returns exactly one column value from one row. The returned value of the scalar subquery expression is the return value of the selected list item of the subquery. If zero rows are returned by the subquery, then the value of the scalar subquery expression is NULL, and if the subquery returns more than one row, then Oracle returns an error.
Limited scalar subqueries were allowed in Oracle8i. Oracle9i allows more. Be careful when using scalar subqueries though. They tend to be resource intensive. There are often more efficient ways of getting at the data you are interested in than using a scalar subquery.
Let's look at some of the scalar subqueries possible in Oracle9i. First, here is an example of a scalar subquery used in the select clause of a SQL statement:
SELECT empno,
(SELECT ename FROM emp b WHERE b.empno=a.mgr) manager
FROM emp a
ORDER BY mgr;
EMPNO MANAGER
---------- ----------
7788 JONES
7902 JONES
7499 BLAKE
7521 BLAKE
7839
In this example, a join between a table called EMP and itself is being created to display the name of the employees' managers. A regular join in this case would probably be more efficient. Here is another example, a scalar subquery in the where clause:
SELECT ename, sal, comm
FROM emp a
WHERE ( (SELECT comm FROM bonus z where
z.empno=a.empno) >
(SELECT AVG(bonus) from historical_bonus WHERE year = 1999 ) );
ENAME SAL COMM
---------- ---------- ----------
FORD 3000 600
MILLER 1300 600
This example prints the employee name and salary for all employees who are getting bonuses that are larger than the average of all 1999 bonuses. Again, a join here would probably be much more efficient. A scalar subquery can also be used in an order by clause, as shown in this example:
SELECT empno, ename, deptno
FROM emp a
ORDER BY (SELECT dname FROM DEPT b where a.deptno=b.deptno);
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10
7369 SMITH 20
7876 ADAMS 20
7902 FORD 20
7788 SCOTT 20
7566 JONES 20
7521 WARD 30
7698 BLAKE 30
7654 MARTIN 30
In this case, the output was ordered by department name, a column that is not readily available in the EMP table, and not even one displayed in the query.
Note that scalar subqueries are still not valid in Oracle9i in the following cases:
As default values for columns
As hash expressions for clusters
In the returning clause of DML statements
In function-based indexes
In check constraints
In when conditions of case expressions
In group by and having clauses
In start with and connect by clauses
In statements that are unrelated to queries, such as create profile