>>>select cast(NVL(LaborTotal,0) + nvl(MateralTotal,0) as N(10,2)) as Total >>>from (select IfKey, sum(hrs*rate) as LaborTotal from Labor group by 1) Labor >>>FULL JOIN (select ifKey, sum(qty*Price) as MaterialPrice from Material group by 1) Material on Labor.ifkey = Material.ifkey>>
>>Select Job.JobNo, (What Naomi said) as Total from Job where job.status='Active' >>>>
>Select Job.JobNo, cast(NVL(LaborTotal,0) + nvl(MateralTotal,0) as N(10,2)) as Total from Jobs FULL OUTER JOIN (material select) Material >on Job.JobKey = Material.JobKey FULL OUTER JOIN (Labor select) Labor on Job.JobKey = Labor.JobKey where Job.Status = 'Active'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.
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)