SQL Server The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression

wsewodh2 于 8个月前发布在其他

关注(0)|答案(1)|浏览(70)

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

sql-server

来源：https://stackoverflow.com/questions/41770598/the-top-or-offset-operator-is-not-allowed-in-the-recursive-part-of-a-recursive-c

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:

SELECT 
-- ...
  MtlRev.RevisionNum,
-- ...
from Erp.PartRev
join (
  select RevisionNum, PartNum,
         ROW_NUMBER() OVER (Partition By PartNum ORDER BY RevisionNum) AS RN
  from Erp.PartRev as MtlRev
  where MtlRev.Approved = 1
) AS MtlRev ON MtlRev.PartNum = Erp.PartMtl.MtlPartNum and MtlRev.RN = 1
-- ...

Note: you may need ORDER BY RevisionNum DESC or some other ordering entirely, I don't know since you didn't say.

赞(0）回复(0）举报 9个月前

我来回答

SQL Server The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression

1条答案

相关问题

热门标签

最新问答