I’ve got a CTE where I’m multiplying an initial cost (decimal) by a quantity (decimal), to get a decimal value. For some reason I am getting the error “Types don’t match between the anchor and the recursive part”. I have tried making the formats identical, but is there something I am missing? Do I need to cast/convert to a specifically formatted decimal? If I remove the quantity from the formula it works fine, so I must be some sort of mismatch, though if they’re both decimals I don’t see what the issue is.
can you post the sql.
I am guessing your initial value is 0 and the query thinks it is an int.
with [SubQuery1] as
(select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
(0) as [Calculated_Hierarchy],
(cast ( substring('........',1 , (Hierarchy + 1) ) + PartMtl.PartNum as nvarchar(25))) as [Calculated_Ind1],
(case
when PartCost.PartNum = @PartNum then
PartMtl.QtyPer * ( PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost )
else 0
end) as [Calculated_InitialCost]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev on
PartMtl.Company = PartRev.Company
and PartMtl.PartNum = PartRev.PartNum
and PartMtl.RevisionNum = PartRev.RevisionNum
and PartMtl.AltMethod = PartRev.AltMethod
and ( PartRev.Approved = true )
inner join Erp.PartCost as PartCost on
PartMtl.Company = PartCost.Company
and PartMtl.MtlPartNum = PartCost.PartNum
where PartMtl.MtlPartNum = @PartNum
union all
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
(SubQuery1.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring('........',1 , (Hierarchy2 + 1) ) + PartMtl1.PartNum as nvarchar(25))) as [Calculated_Ind2],
(SubQuery1.Calculated_InitialCost * PartMtl1.QtyPer) as [Calculated_Cost]
from SubQuery1 as SubQuery1
inner join Erp.PartMtl as PartMtl1 on
SubQuery1.PartMtl_Company = PartMtl1.Company
and SubQuery1.PartMtl_PartNum = PartMtl1.MtlPartNum
inner join Erp.PartRev as PartRev1 on
PartMtl1.Company = PartRev1.Company
and PartMtl1.PartNum = PartRev1.PartNum
and PartMtl1.RevisionNum = PartRev1.RevisionNum
and PartMtl1.AltMethod = PartRev1.AltMethod
and ( PartRev1.Approved = true ))
select
[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation]
from SubQuery1 as SubQuery11For the Calculated_InitialCost what is the type?
You might need to cast it to decimal
CAST( case
when PartCost.PartNum = @PartNum then
PartMtl.QtyPer * ( PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost )
else 0
end as decimal)
I am trying to stack up the total cost of the lowest level part (input parameter) in a BOM. I only care about the lowest level, so my InitialCost formula ignores any parts that aren’t equal to input parameter. That is probably causing an issue because at each recursion it’s trying to multiply that field (which is 0 for any higher levels). This is probably causing the error but also I need to find a way to get that initial cost to be factored in at each level.
Calculated_InitialCost is a decimal.
I applied a cast to both InitialCost and Cost and the error is gone.
Cool. You can always do the costing outside of the CTE. That way you are only referencing the costing tables once vs each time in the loop.
But if you’re not including the costing in the loop, then how can you stack up the cost (there might be 5 washers at the lowest level, but that might go into a part that has a quantity of 2 in the top level, making there 10 washers at the upper level)?
That is driven from your part qty though. The costs is just math based on the qty needed.
by the way I love these types of queries. I am asking as a way to improve, not trying to be critical.
also just letting you know as I got caught with this one. Your query assumes that there is only ONE approved rev for the part. If there are multiple things might not look right. Sounds like you have many levels to BOM this may come into play.
Oh no, I didn’t think you were being critical. This is my first foray into these and so I’m learning a lot. It is likely I could do it outside of the CTE but it’s hard to wrap my head around…not sure it would know which part goes to which.
Keep posting then as you get stuck.
Right now I am trying to only include the most recent revision. Not sure if I can accomplish this without another subquery.
correct it will be a couple of subqueries.
It will end up looking something like this. Granted you will have to make changes for your situation.
with [BOMReviewParent] as
(select
(Part.ShortChar10) as [Calculated_Customer],
(PartRev.PartNum) as [Calculated_TopPart],
(PartRev.RevisionNum) as [Calculated_TopPartRev],
(part.PartNum) as [Calculated_ParentPartNum],
(PartRev.RevisionNum) as [Calculated_ParentRevNum],
(part.PartNum) as [Calculated_ChildPartNum],
(PartRev.RevisionNum) as [Calculated_ChildRevNum],
(0) as [Calculated_lv],
(Cast(part.PurchasingFactor as int)) as [Calculated_QtyPer],
(Cast(part.PartDescription as nvarchar(500))) as [Calculated_PartName],
(0) as [Calculated_OpSeq],
(CAST(part.PartNum + '-' + partrev.RevisionNum as nvarchar(500))) as [Calculated_Sort]
from dbo.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
and Part.InActive = 0
and Part.ClassID in ( 'FGD', 'FGA')
and PartRev.Approved = 1
union all
select
[BOMReviewParent].[Calculated_Customer] as [Calculated_Customer],
[BOMReviewParent].[Calculated_TopPart] as [Calculated_TopPart],
[BOMReviewParent].[Calculated_TopPartRev] as [Calculated_TopPartRev],
[BOMChildren].[PartMtl_PartNum] as [PartMtl_PartNum],
[BOMChildren].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[BOMChildren].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[BOMChildren].[Calculated_RevNum] as [Calculated_RevNum],
(BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
(cast(BOMChildren.Calculated_ChildQtyPer as int)) as [Calculated_BOMQtyPer],
(CAST(REPLICATE ('| ' , BOMReviewParent.Calculated_lv +1) + BOMChildren.PartBOMChild_PartDescription as nvarchar(500))) as [Calculated_BOMPartName],
(BOMChildren.Calculated_MtlSeq) as [Calculated_BOMMtlSeq],
(CAST(BOMReviewParent.Calculated_Sort + '-' + RIGHT( '0000'+ Convert(varchar, BOMChildren.Calculated_SeqNum), 4)as nvarchar(500))) as [Calculated_BOMSort]
from (select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
(((select
[PartRevA].[RevisionNum] as [PartRevA_RevisionNum]
from Erp.PartRev as PartRevA
inner join (select PartRevB.Company,
[PartRevB].[PartNum] as [PartRevB_PartNum],
[PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
(ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY partrevb.ApprovedDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
where (PartRevB.Approved = 1 and PartRevB.EffectiveDate <= getdate() and PartRevB.PartNum = [PartMtl].[MtlPartNum] )) as PartRevB1 on
PartRevA.PartNum = PartRevB1.PartRevB_PartNum
and PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
and PartRevA.Company = PartRevB1.Company
where PartRevB1.Calculated_PartRevB_RowNum = 1))) as [Calculated_RevNum],
(cast(PartMtl.QtyPer as int)) as [Calculated_ChildQtyPer],
[PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
(cast(PartMtl.MtlSeq as nvarchar(254))) as [Calculated_SeqNum],
(CAST(PartMtl.MtlSeq as Int)) as [Calculated_MtlSeq]
from Erp.PartMtl as PartMtl
inner join dbo.Part as PartBOMChild on
PartMtl.Company = PartBOMChild.Company
and PartMtl.MtlPartNum = PartBOMChild.PartNum) as BOMChildren
inner join BOMReviewParent as BOMReviewParent on
BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
and BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum)When I run the query I am getting zero records…not sure what I’m doing wrong.
with [SubQuery1] as
(select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[MaxEffDate2].[PartRev3_RevisionNum] as [PartRev3_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
(0) as [Calculated_Hierarchy],
(cast ( substring('........',1 , (Hierarchy + 1) ) + PartMtl.PartNum as nvarchar(25))) as [Calculated_Ind1],
(cast(case
when PartCost.PartNum = @PartNum then
PartMtl.QtyPer * ( PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost )
else 0
end as decimal(10,2))) as [Calculated_InitialCost],
(cast(PartMtl.QtyPer as decimal(10,2))) as [Calculated_Qty]
from Erp.PartMtl as PartMtl
inner join Erp.PartCost as PartCost on
PartMtl.Company = PartCost.Company
and PartMtl.MtlPartNum = PartCost.PartNum
where PartMtl.MtlPartNum = @PartNum
union all
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
(SubQuery1.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring('........',1 , (Hierarchy2 + 1) ) + PartMtl1.PartNum as nvarchar(25))) as [Calculated_Ind2],
(cast(SubQuery1.Calculated_InitialCost * PartMtl1.QtyPer as decimal(10,2))) as [Calculated_Cost],
(cast(SubQuery1.PartMtl_QtyPer * PartMtl1.QtyPer as decimal (10,2))) as [Calculated_Qty]
from SubQuery1 as SubQuery1
inner join Erp.PartMtl as PartMtl1 on
SubQuery1.PartMtl_Company = PartMtl1.Company
and SubQuery1.PartMtl_PartNum = PartMtl1.PartNum
and SubQuery1.PartRev3_RevisionNum = PartMtl1.RevisionNum)
select
[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartRev3_RevisionNum] as [PartRev3_RevisionNum],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1],
(case
when PartCost1.PartNum = @PartNum then
PartCost1.StdLaborCost + PartCost1.StdBurdenCost + PartCost1.StdMaterialCost + PartCost1.StdSubContCost + PartCost1.StdMtlBurCost
else 0
end) as [Calculated_UnitCost],
[SubQuery11].[Calculated_InitialCost] as [Calculated_InitialCost],
[SubQuery11].[Calculated_Qty] as [Calculated_Qty]
from SubQuery1 as SubQuery11
inner join Erp.PartCost as PartCost1 on
SubQuery11.PartMtl_Company = PartCost1.Company
and SubQuery11.PartMtl_MtlPartNum = PartCost1.PartNumPart of my problem was I was putting a part number in that didn’t have any parent parts, but I ended up moving the “most recent revision” subquery to the top level.