The use of case in Oracle

Case expressions can implement If-then-else logic in SQL without having to use Pl/sql. Case works like Decode (), but case should be used because it is compatible with ANSI.
There are two types of expressions in the case:

1. A simple case expression that uses an expression to determine the return value.

Grammar:

Case Search_expression when expression1 THEN result1 when expression2 THEN result2 ... When Expressionn THEN resultn ELSE Default_result End

Cases:

Select product_id,product_type_id, Case product_type_id when 1 then "book" When 2 Then "video" when 3 Then ' DVD ' when 4th En ' CD ' else ' Magazine ' end

Results:

product_id product_type_id Caseprod
---------- --------------- --------
1 1 book
2 1 book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
4 CD
One 4 CD
Magazine

Rows selected.

2. Search case expressions, using conditions to determine the return value.

Grammar:

When the case is Condition1 THEN result1 when Condistion2 THEN result2 ... When Condistionn THEN resultn ELSE Default_result End

Cases:

Select product_id,product_type_id,
Case
When Product_type_id=1 then ' book '
When product_type_id=2 then ' video '
When product_type_id=3 then ' DVD '
When product_type_id=4 then ' CD '
Else ' Magazine '
End
From Products

The results are the same.

+ ——————————————————————————————————————————————————

In ANSI SQL 1999, there are four types the 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 is used to search and then replace a given value within a given SQL Statement. This is example:

SELECT ename,
(Case Deptno
When ten THEN ' ACCOUNTING '
When THEN ' a '
When THEN ' SALES '
When THEN ' OPERATIONS '
ELSE ' Unassigned '
End) as Department
from EMP;
Ename DEPARTMENT
---------- ----------
SMITH
ALLEN Unassigned
WARD SALES
JONES
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT
KING ACCOUNTING
TURNER SALES
ADAMS
JAMES SALES
FORD
MILLER ACCOUNTING
In this example, if the Deptno column has a in it, the SQL query would return the value accounting rather than the Numbe R 10. If The Deptno is isn't, then the case statement'll fall through to the ELSE clause, which would return UN Assigned. Note this with a simple case statement, no comparison operators can be used.

Searched case statements
The searched case statement is the most powerful cousin of the simple case statement. The searched case statement are like a if...then...else structure, and can be used to conditionally search and replace values Using logical operators and multiple conditions. Let ' s look at a example:

SELECT ename, Sal, Deptno,
Case
When Sal <= 0 Then
When Sal > 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, your are trying to determine how much of the a bonus each employee are eligible for. The bonus amount is based on the salary of the employee, but notice this some conditions have been added based on what DEP Artment number the employee is in. You can have a searched case statement can have many different when clauses, and so you can apply many the criteria in T Hose clauses to get the answers for you need.

Nullif and COALESCE
To further comply with SQL 1999, the NULLIF and coalesce statements have been to added. 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 ' the ' the ' the ' the ' is ' argument by the function. This is a example of 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 are the Commision for an employee) has a 0 value, it'll be returned as a NULL As shown in the sample output.

The COALESCE statement is a bit like the Oracle NVL function. This is 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 would be returned. Note this with coalesce, there are no implicit type conversion of the arguments passed to it, so the following code would n OT 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 This 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 R Eturns more than one row, then Oracle returns an error.

Limited scalar subqueries were allowed in oracle8i. Oracle9i allows more. Is careful when using scalar subqueries though. They tend to be resource intensive. There are often more efficient ways of getting to the # you are interested in than using a scalar subquery.

Let's look at some of the scalar subqueries possible in oracle9i. A scalar subquery used in the SELECT clause of 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 the case would probably is more efficient. This is another example, a scalar subquery the WHERE clause:

Select ename, Sal, Comm
from EMP a
where (SELECT comm from bonus Z where
     &nbs p;          z.empno=a.empno) >
(SELECT AVG (bonus) from Historical_bonus WHERE year = 1999));
ename             SAL        COMM
------------------------------
ford              3000       
miller            1300       
This example prints the Employee name and salary for all employees who are getting bonuses this are larger than the average of all 1999. Again, a join here would probably is much more efficient. A scalar subquery can also is 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 being ordered by department name, a column, isn't readily available in the EMP table, and not Even one displayed in the query.

Note This 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 the conditions of case expressions
In GROUP BY and has clauses
In start with and connect by clauses
In statements so are unrelated to queries, such as Create profile