Last Updated: February 25, 2016

Use SQL WITH clause as a filter

here I am trying to find the total cost of a PROJECT. This must be done by finding all records in the JOB table that correspond with a particular project. I have to join through a table called SPECIFICATION. I then need to use these records to find all MATERIALREQUEST and SCHEDULE records for that project relating to that job. Conventionally this would require the same subquery to join PROJECT with JOB for both subqueries on MATERIALREQUEST and SCHEDULE, but using the with clause allows me to write this once as a sort of temporary table, JOBS, and use it twice in my code.

Note that this is in Oracle.

with JOBS as (
 select p.PID, j.*
 from "PROJECT" p 
 join "SPECIFICATION" s on p.PID = s.PID
 join "JOB" j on s.SPECID = j.SPECID
 where p.STATUS = 1
)
select p.PID as "PROJECT NUMBER", (et.TOTAL + mt.TOTAL) as "TOTAL COST"
from "PROJECT" p 
 join (
 select sum(e.HOURLYRATE * j.ACTDURATION) as TOTAL, j.PID
 from JOBS j 
 join SCHEDULE s on j.JOBID = s.JOBID
 join EMPLOYEE e on s.EMPID = e.EMPID
 group by j.PID
 ) et on p.PID = et.PID 
 join (
 select sum(m."COST" * r.QTY) as TOTAL, j.PID
 from JOBS j
 join MATERIALREQUEST r on j.JOBID = r.JOBID
 join MATERIAL m on r.MATID = m.MATID
 where r.AUTH = 1
 group by j.PID
 ) mt on p.PID = mt.PID
where p.STATUS = 2 and p.pid=2452;

#sql

#oracle

#with