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;
Written by Brian Jesse
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Sql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#