devxlogo

SubQueries in the FROM Clause of SELECT

SubQueries in the FROM Clause of SELECT

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

devx-admin

Share the Post: