>Here's what works fine, thanks to you. Notice your part betweren the arrows in the code below. You have helped me very much here.
>
>Now, let me say that I am already calculating the Material cost and Labor cost as separate summed fields, but I also want to add them togther in a separate column. So, how can you just just add fields that are not natural fields in the tables, but they are derived fields in the SQL (using the "as" clause). You will see " (Select sum(...) ) as Material" and " (Select sum(...)) as Labor" in the code below. It seems crazy to have to re-calculate both of those child data sets again, just to add them together, when I've already done that for each of them sepatetely in the preceeding SQL.
>
>
>

>Select job_item.job_num,
>       job_info.job_start as date,
>       job_info.p_o_num as po_num,
>       job_item.qty,
>       job_item.part_no,
>       nvl(parts.altpartno,'') as Alt_Part_No,
>       job_item.dwg_no,
>       nvl(parts.desc, job_item.desc) as descrip,
>       job_item.price,
>       job_item.qty*job_item.price as total,
>       job_info.quote_no,
>       job_info.est_cost,
>       (Select Cast(Sum(mat_qty*mat_cost*job_info.mat_mkup) as N(10,2)) From Material Where mat_code=Job_Info.job_num) as Material,
>       (Select Cast(Sum(hrs_worked*mach_rate) as N(10,2)) From Actijobs Where job_num=Job_Info.job_num) as Labor,
>       (Select Cast(Sum(hrs_worked) as N(10,2)) From Actijobs Where job_num=Job_Info.job_num) as Hours,
>---->  (Select cast(NVL(LaborTotal,0) + nvl(MaterialTotal,0) as N(10,2)) 
>|         From (Select job_num as lbrkey, sum(hrs_worked*mach_rate) as LaborTotal from Actijobs  group by 1) Labor
>|         Full Join (Select mat_code as mtlkey, sum(mat_qty*mat_cost*Job_Info.mat_mkup) as MaterialTotal from Material group by 1) Material on Labor.lbrkey = Material.mtlkey
>---->     Where Labor.lbrkey=Job_Info.job_num) as TotalCost,
>       job_info.job_invnum as Inv_Num,
>        (Select Sum(amount) From Job_Invoice Where job_num=Job_Info.job_num) as Inv_Amt,
>       job_info.cust_num,
>       space(6) as Vendor
>       From job_item
>             left outer join Job_Info on job_item.job_num=job_info.job_num
>             left outer join Parts on job_item.ipartkey=parts.ipkey
>...    (where, order by, and into clause goes here, dynamically added)
>
>

Matt,

The way it's written now is inefficient. Using it as derived tables (as I showed) should be quick enough, but using them as projection (if this is how it's called) is very inefficient. I can not re-write it right now for you - need to concentrate on my work.

Review the above query and try re-writing yours in better way.