- CASE allows for conditional processing of returned rows.
- CASE returns a single result for each row processed.
- Each row is evaluated against each WHEN clause.
- First match returns a result for that row.
- If no match, ELSE result is produced for that row.
CASE value-expr WHEN expr1 THEN result1
WHEN expr2 THEN result2
:
ELSE resultn END
Example:
Calculate the fraction of the total salary of all employees represented by the salaries of Dept. 401.
SELECT SUM(
CASE department_number
WHEN 401 THEN salary_amount
ELSE 0
END) / SUM(salary_amount)
FROM employee;
Result:
(Sum(
-------------------------------------------------------------
2.22834717118098E-001
Example 2:
Get the total salaries for departments 401 and 501.
SELECT CAST (SUM(
CASE department_number
WHEN 401 THEN salary_amount WHEN 501 THEN salary_amount
ELSE 0
END) / AS NUMERIC (9,2))
AS total_sals_401_501
FROM EMPLOYEE;
Result:
total sals 401 501
-------------------
4457000.00
Searched CASE Statement
CASE WHEN condition1 THEN value-expr1
WHEN condition2 THEN value-expr2
:
:
:
ELSE value-expr END
Example:
SELECT last_name,
CASE WHEN salary_amount < 30000 THEN 'Under $30K'
WHEN salary_amount < 40000 THEN 'Under $40K'
WHEN salary_amount < 50000 THEN 'Under $50K'
ELSE
’< = $50K'
END
FROM employee
ORDER BY salary_amount;
Result:
last_name
-------------- ------------------------
Phillips Under $30K
Crane Under $30K
Hoover Under $30K
Rabbit Under $30K
Kanieski Under $30K
Stein Under $30K
Lombardo Under $40K
Ryan Under $40K
Machado Under $40K
Short Under $40K
Johnson Under $40K
Trader Under $40K
Hopkins Under $40K
Morrissey Under $40K
Charles Under $40K
Brown Under $50K
Brown Under $50K
Rogers Under $50K
Villegas Under $50K
Daly Over $50K
Wilson Over $50K
Ratzlaff Over $50K
Rogers Over $50K
Kubic Over $50K
Runyon Over $50K
Trainer Over $50K
