Simple CASE function
Syntax
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Examples
• SELECT Title,
CASE movie_type
WHEN 1 THEN 'Horror'
WHEN 2 THEN 'Comedy'
WHEN…
ELSE NULL
END,
our_cost
FROM movie_titles;
• SELECT COUNT(empno), MAX(sal), AVG(comm) FROM emp GROUP BY CASE WHEN deptno BETWEEN 1 and 5 THEN 'Group 1' ELSE 'Group 2' END;
• SELECT * FROM emp ORDER BY CASE MOD(deptno, 2) WHEN 0 THEN 'Group 1' ELSE 'Group 2' END;
• SELECT COUNT(*), deptno FROM emp GROUP BY deptno HAVING CASE MOD(deptno, 2) WHEN 0 THEN MAX(sal) ELSE MAX(comm) END > AVG(sal);
