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;