Last Updated: February 25, 2016
·
1.52K
· bajesse

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;