I am attempted to created a costed bill of materials with a recursive CTE query. The issue I'm running into is I need to select a single part revision for the material parts but recursive queries do not allow for the use of the TOP operator. To give more information on the problem:
- A part revision (PartRev table) has as bill of materials which consists of many materials(PartMtl Table).
- The materials on a bill of material may also have their own materials. To get this we need the material part (already have this) and its revision. However, the material part revision is not logged by our ERP system (we did not develop the ERP system, so we cannot change this). It is rather calculated on the fly and a single record selected.
- SQL Server does not allow for the use of the TOP operator within the recursive query, so I am unable to select a single revision.
I am unsure of what I can do to work around this. Below is the SQL code I attempted.
WITH CostedBOMFinance AS
(
select
Erp.PartRev.PartNum,
Erp.PartRev.RevisionNum,
case when Erp.PartMtl.ViewAsAsm = 1 then 'Asm' else 'Mtl' end as MtlType,
Erp.PartMtl.MtlSeq as Seq,
Erp.PartMtl.MtlPartNum,
(
select TOP(1) Erp.PartRev.RevisionNum
from Erp.PartRev as MtlRev
where MtlRev.Approved = 1 and
MtlRev.PartNum = Erp.PartMtl.MtlPartNum
) as MtlRev,
Erp.Part.PartDescription,
Erp.PartMtl.QtyPer,
Erp.PartMtl.UOMCode,
Erp.PartCost.StdBurdenCost,
Erp.PartCost.StdLaborCost,
Erp.PartCost.StdMaterialCost as StdUnitCost,
Erp.PartCost.StdSubContCost,
Erp.PartCost.StdBurdenCost + Erp.PartCost.StdLaborCost + Erp.PartCost.StdMaterialCost + Erp.PartCost.StdSubContCost as TotalStdCost,
1 as Level
from Erp.PartRev
join Erp.PartMtl on Erp.PartMtl.Company = Erp.PartRev.Company and
Erp.PartMtl.PartNum = Erp.PartRev.PartNum and
Erp.PartMtl.RevisionNum = Erp.PartRev.RevisionNum
join Erp.Part on Erp.Part.Company = Erp.PartMtl.Company and
Erp.Part.PartNum = Erp.PartMtl.MtlPartNum
join Erp.PartCost on Erp.PartCost.Company = Erp.Part.Company and
Erp.PartCost.PartNum = Erp.Part.PartNum
UNION ALL
select
Erp.PartRev.PartNum,
Erp.PartRev.RevisionNum,
case when Erp.PartMtl.ViewAsAsm = 1 then 'Asm' else 'Mtl' end as MtlType,
Erp.PartMtl.MtlSeq as Seq,
Erp.PartMtl.MtlPartNum,
(
select TOP(1) MtlRev.RevisionNum
from Erp.PartRev as MtlRev
where MtlRev.Approved = 1 and
MtlRev.PartNum = Erp.PartMtl.MtlPartNum
) as MtlRev,
Erp.Part.PartDescription,
Erp.PartMtl.QtyPer,
Erp.PartMtl.UOMCode,
Erp.PartCost.StdBurdenCost,
Erp.PartCost.StdLaborCost,
Erp.PartCost.StdMaterialCost as StdUnitCost,
Erp.PartCost.StdSubContCost,
Erp.PartCost.StdBurdenCost + Erp.PartCost.StdLaborCost + Erp.PartCost.StdMaterialCost + Erp.PartCost.StdSubContCost as TotalStdCost,
Level + 1
from Erp.PartRev
join Erp.PartMtl on Erp.PartMtl.Company = Erp.PartRev.Company and
Erp.PartMtl.PartNum = Erp.PartRev.PartNum and
Erp.PartMtl.RevisionNum = Erp.PartRev.RevisionNum
join Erp.Part on Erp.Part.Company = Erp.PartMtl.Company and
Erp.Part.PartNum = Erp.PartMtl.MtlPartNum
join Erp.PartCost on Erp.PartCost.Company = Erp.Part.Company and
Erp.PartCost.PartNum = Erp.Part.PartNum
join CostedBOMFinance as CostedBOMFinanceParent on CostedBOMFinanceParent.PartNum = Erp.PartMtl.MtlPartNum and
CostedBOMFinanceParent.RevisionNum = MtlRev
)
select * from CostedBOMFinance
1条答案
按热度按时间8yoxcaq71#
You need to use a windowing function in this case you can use it in a sub query which you join to, like this:
Note: you may need
ORDER BY RevisionNum DESC
or some other ordering entirely, I don't know since you didn't say.