>>>How would you select a sum of fields from 2 child tables for the parent key?
>>>
>>>Here is the pseudo code of what I need:
>>>
>>>select (sum(hrs*rate) from Labor where Labor.ifkey=Job.ipkey) + (sum (qty*price) from Material where Material.ifkey=Job.ipey) as TotalCost
>>
>>Using derived tables (VFP9 only), of course.
>>
>>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
>
>Looks promising, I'm gonna try it right now.
>
>One thing I forgot to mention is, I need this total to be one field in the resulting cursor and there will but other fields too, like this:
>
>JobNo Total
>===== ==============
>124345 12,510.65
>643328 52,710.83
>
>
>Like this:
>
>
>Select Job.JobNo, (What Naomi said) as Total from Job where job.status='Active'
>
>
>Will that work?
Something like this
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'
If it's not broken, fix it until it is.
My Blog