Could you please help me understand recursive queries in SQL? I have been stuck for one week trying to debug

Transact-SQL
1

with [SubQuery1] as
(select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(1) as [Level],
[PartMtl].[Company] as [PartMtl_Company],
(cast(1 as decimal)) as [Calculated_ParentQty],
[PartRev2].[AltMethod] as [PartRev2_AltMethod],
(PartMtl.PartNum) as [Calculated_TopPartNum],
(PartMtl.RevisionNum) as [Calculated_TopRevNum]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev2 on
PartMtl.Company = PartRev2.Company
and PartMtl.PartNum = PartRev2.PartNum
WHERE
PartMtl.PartNum = 'CPB100PC-S-0' AND
PartRev2.RevisionNum = 'A' AND
PartMtl.Company = '30901' AND
PartRev2.EffectiveDate = '2022-02-14'

union all

select
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Level + 1) as [Lvl],
[PartMtl1].[Company] as [PartMtl1_Company],
-- (cast(PartMtl_QtyPer as decimal)) as [Calculated_ParentQty],
[SubQuery1].[PartRev2_AltMethod] as [PartRev2_AltMethod]
-- [SubQuery1].[Calculated_TopPartNum] as [Calculated_TopPartNum],
-- [SubQuery1].[Calculated_TopRevNum] as [Calculated_TopRevNum]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.Company = SubQuery1.PartMtl_Company
and PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum
and PartMtl1.AltMethod = SubQuery1.PartRev2_AltMethod
inner join Erp.PartRev as PartRev on
PartMtl1.Company = PartRev.Company
and PartMtl1.PartNum = PartRev.PartNum
and PartMtl1.RevisionNum = PartRev.RevisionNum
and (PartRev.Approved = 'True' ))

select
[SubQuery11].[Level] as [Level],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(SubQuery11.Calculated_ParentQty * SubQuery11.PartMtl_QtyPer) as [Calculated_ExtQty],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[SubQuery11].[Calculated_TopPartNum] as [Calculated_TopPartNum],
[SubQuery11].[Calculated_TopRevNum] as [Calculated_TopRevNum]
from SubQuery1 as SubQuery11
left outer join Erp.PartCost as PartCost on
SubQuery11.PartMtl_Company = PartCost.Company
and SubQuery11.PartMtl_MtlPartNum = PartCost.PartNum
and PartCost.CostID = 1
inner join Erp.Part as Part on
SubQuery11.PartMtl_Company = Part.Company
and SubQuery11.PartMtl_MtlPartNum = Part.PartNum
ORDER BY Level;

2

It will be difficult for anyone to help you without sample data and the expected results for that data.

I have attempted to tidy up your query. You will need to provide a small amount of sample data for the BaseQuery CTE along with the expected results. (Make sure it is anonymized!)

WITH BaseQuery
AS
(
	SELECT M.PartNum,M.RevisionNum,M.MtlPartNum,M.QtyPer,M.RelatedOperation,M.PullAsAsm,M.ViewAsAsm,M.PlanAsAsm
		,M.Company
		,R.AltMethod
		,R.RevisionNum AS RevRevisionNum
		,R.EffectiveDate
		,R.Approved
	FROM Erp.PartMtl M
		JOIN Erp.PartRev R
			ON M.Company = R.Company
				AND M.PartNum = R.PartNum
)
,RLevels
AS
(
	SELECT PartNum,RevisionNum,MtlPartNum,QtyPer,RelatedOperation,PullAsAsm,ViewAsAsm,PlanAsAsm
		,1 AS RLevel
		,Company
		-- No idea of the precision and scale. Default precision is 18
		,CAST(1 AS decimal(18)) AS ParentQty
		,AltMethod
		,PartNum AS TopPartNum
		,RevisionNum AS TopRevNum
	FROM BaseQuery
	WHERE PartNum = 'CPB100PC-S-0'
		AND RevRevisionNum = 'A'
		AND Company = '30901'
		AND EffectiveDate = '2022-02-14'

	UNION ALL

	SELECT B.PartNum,B.RevisionNum,B.MtlPartNum,B.QtyPer,B.RelatedOperation,B.PullAsAsm,B.ViewAsAsm,B.PlanAsAsm
		,R.RLevel + 1
		,B.Company
		-- No idea of the precision and scale. Default precision is 18
		,CAST(B.QtyPer AS decimal(18))
		,R.AltMethod
		,R.TopPartNum
		,R.TopRevNum
	FROM RLevels R
		JOIN BaseQuery B
			ON R.Company = B.Company
				AND R.PartNum = B.PartNum
				AND R.RevisionNum = B.RevisionNum
	WHERE B.Approved = 'True'
)
SELECT R.RLevel
	,R.MtlPartNum
	,R.RelatedOperation
	,R.QtyPer
	-- This looks wrong
	,R.ParentQty * R.QtyPer AS ExtQty
	,R.PartNum
	,R.RevisionNum
	,R.TopPartNum
	,R.TopRevNum
FROM RLevels R
/* Can see no point in this as not in SELECT and outer join will not restrict results
	LEFT JOIN Erp.PartCost C
		ON R.Company = C.Company
			AND R.PartNum = C.PartNum
			AND C.CostID = 1
*/
	JOIN Erp.Part P
		ON R.Company = P.Company
			AND R.PartNum = P.PartNum
ORDER BY RLevel;

Also, you had better read the following: