Oracle CASE SQL statement

Don Burleson

•

The CASE statement is like a series of IF statements, only using the key word WHEN. A CASE statement is evaluated from top to bottom. If a condition is true, then corresponding THEN clause is executed and execution jumps to the END CASE (short circuit evaluation) clause.

Consider this pseudo-code example:

case
when n_numb = 1 then v_status := 'very small'
when n_numb < 4 then v_status := 'small'
when n_numb = 5 then v_status := 'even'
when n_numb > 4 then v_status := 'large'
else v_status := 'very large'
end case;

Oracle SQL allows you to add "Boolean logic" and branching using the decode and CASE clauses. The case statement is a more flexible extension of the Decode statement. In its simplest form the Oracle CASE function is used to return a value when a match is found:

SELECT last_name, commission_pct,
  (CASE commission_pct 
    WHEN 0.1 THEN 'Low' 
    WHEN 0.15 THEN 'Average'
    WHEN 0.2 THEN 'High' 
    ELSE 'N/A' 
  END ) Commission
FROM employees ORDER BY last_name;

•

A more complex version is the Searched CASE expression where a comparison expression is used to find a match:

SELECT last_name, job_id, salary,
  (CASE
    WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
    WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
    WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
    WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
    ELSE 'NOT APPLICABLE'
  END ) Raise
FROM employees;

Read more on CASE:

The PL/SQL CASE Statement

Use a CASE Statement in Oracle SQL

This assignment will ask you to modify an existing report to use the CASE statement. Your assignment is to use a CASE statement as follows:

* CASE 1: Books with total sales greater than $100,000, display “Best Seller”

* CASE 2: Books with total sales between $10,000 and $99,999 display “Average Seller”

* CASE 3: Books with sales less than $10,000 display “Poor Seller”

ANSWER:

col store_name format a25
col book_title format a25
col total_sales format $999,999
col sales format a15

break on sales skip 2

select
(case
   when sum(quantity)*book_retail_price > 100000 then 'Best Seller'
   when sum(quantity)*book_retail_price < 10000 then 'Poor Seller'
   else 'Average Seller'
   end ) sales,
   store_name,
   book_title,
   sum(quantity)*book_retail_price total_sales
from
   store,
   sales,
   book
where
   store.store_key = sales.store_key
and
   sales.book_key = book.book_key
group by
   store_name,
   book_title,
   book_retail_price
order by
   total_sales desc
;

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books