When a SubQuery is used in a FROM clause instead of a Table name, Oracle executesthe subquery and then uses the resulting rows as a view in the FROMclause. It’s a very useful method for performing reports with various types of counts.
Consider the following classic query, which counts the number of awards accepted or rejected by employees. It does an outer join to take care ofemployees who might have only accepted or only rejected awards. This willgive one row per employee in an emp_status table:
select a.id, nvl(accepted, 0) TotAccepted,
nvl(rejected, 0) TotRejected,fnamefrom emp_status a, (select count(*) accepted, id,
code , status from emp_status where code = 'AWARD' and status = 'A' group by id, code, status ) b , (select count(*) rejected, id,
code , status from emp_status where code = 'AWARD' and status = 'R' group by id, code, status) c , (select id, fname from employee) dwhere a. code = 'AWARD'and a.status in ('R', 'A')and a.id = b.id(+)and a.id = c.id(+)and a.id = d.idgroup by a.id, accepted, rejected, fname
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
