The example below retrieves a sample list of employees, their job roles, and how many hours they have billed to a particular customer.
select e.emp_name, e.job_role, sum(b.hours) from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role; emp_name job_role sum(b.hours) --------- ------------- ------------ Sandy attorney 10 Bob programmer 4 Jill programmer 8 John accountant 32 Barbara secretary 20 Danny proj manager 8
The company has a simple billing rule. Attorney hours are billed at $200 per hour, programmers at $100 per hour, accountants at $100 per hour, and all others at $25 per hour. We can use the case statement to help us derive how much to bill the customer.
select e.emp_name, e.job_role, sum(b.hours), case when job_role='attorney' then sum(b.hours)*200 when job_role='programmer' then sum(b.hours)*100 when job_role='accountant' then sum(b.hours)*100 else charge=sum(b.hours)*25 end as charge from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role; emp_name job_role sum(b.hours) charge --------- ------------- ------------- ------- Sandy attorney 10 200 Bob programmer 4 400 Jill programmer 8 800 John accountant 32 3200 Barbara secretary 20 500 Danny proj manager 8 200
Of course, this is just a hands-on illustration of the case expression in Oracle SQL. In real life, it is arguable that this type of business logic should probably be written elsewhere, perhaps with the base rates stored in a table, so that it can be easily modified if need be.