CTE for Recursion Between [PegDmdMst] and [JobHead]

Epicor ERP 10
,
1

Good afternoon.

Might someone be able to evaluate the following SQL (CTE) and determine why it surpasses the maximum recursion level?

WITH
	cte
	(
		Company
		, DemandSeq
		, DemandOrdNum
		, DmdPartNum
		, DemandDate
		, DemandQty
		, JMJobNum
		, MtlPartNum
		, Lvl
	)
AS
(
SELECT
	PDM.Company
	, PDM.DemandSeq
	, CAST(PDM.DemandOrdNum AS VARCHAR (20))
	, PDM.PartNum AS DmdPartNum
	, PDM.DemandDate
	, PDM.DemandQty
	, CAST(JH.JobNum AS VARCHAR (20)) AS JMJobNum
	, JH.PartNum AS MtlPartNum
	, 0
FROM
	Erp.PegDmdMst AS PDM
		INNER JOIN Erp.JobHead AS JH
			ON JH.Company = PDM.Company
			AND JH.JobNum = PDM.DemandOrdNum
WHERE
	PDM.PartNum = '<INSERT RELEVANT PARTNUM HERE>'

UNION ALL

SELECT
	PDM.Company
	, PDM.DemandSeq
	, CAST(PDM.DemandOrdNum AS VARCHAR (20))
	, PDM.PartNum AS DmdPartNum
	, PDM.DemandDate
	, PDM.DemandQty
	, CAST(JH.JobNum AS VARCHAR (20)) AS JMJobNum
	, JH.PartNum AS MtlPartNum
	, Lvl + 1
FROM
	Erp.PegDmdMst AS PDM
		INNER JOIN Erp.JobHead AS JH
			ON JH.Company = PDM.Company
			AND JH.JobNum = PDM.DemandOrdNum
		INNER JOIN cte
			ON cte.Company = JH.Company
			AND cte.DemandOrdNum = JH.JobNum
)
SELECT DISTINCT
	Company
	, DemandSeq
	, DemandOrdNum
	, DmdPartNum
	, DemandDate
	, DemandQty
	, JMJobNum
	, MtlPartNum
	, Lvl
FROM
	cte
WHERE
	Lvl < 3

I did try the “OPTION (MAXRECURSION 200)” as part of the statement, but that didn’t work either. I also increased the “MAXRECURSION” value to “30000” (which about 2000 shy of the hard recursion boundary), which also failed.

Thanks for taking a look at it.

2

Usually it happens when your CTE does an infinite loop. I dont know much about that table but I needed to be very specific what level 0 is and what is it not.

It usually had to do with the 2 UNIONs being too identical.

Example:

PartMtl
UNION
PartMtl

Correct Way:
PartRev
UNION
PartMtl

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [PartRevCTE] as 
(select 
	[PartRev].[Company] as [PartRev_Company],
	(CAST('' as nvarchar(50))) as [Calculated_Parent],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[AltMethod] as [PartRev_AltMethod],
	(CAST(PartRev.PartNum as nvarchar(50))) as [Calculated_Child],
	(0) as [Calculated_Level],
	(CAST('\' + '' + '\' + PartRev.PartNum + '\' as nvarchar(500))) as [Calculated_Sort]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
	and Part.PartNum = PartRev.PartNum
	and {GetLatestApprovedRev} = PartRev.RevisionNum
where (Part.PartNum = @sPartNum)
union all
select 
	[PartMtl].[Company] as [PartMtl_Company],
	(CAST(PartMtl.PartNum as nvarchar(50))) as [Calculated_Parent],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[AltMethod] as [PartMtl_AltMethod],
	(CAST(PartMtl.MtlPartNum as nvarchar(50))) as [Calculated_Child],
	(PartRevRecursion.Calculated_Level + 1) as [Calculated_Level],
	(CAST(PartRevRecursion.Calculated_Sort + '\' + PartMtl.MtlPartNum + '\' as nvarchar(500))) as [Calculated_Sort]
from  PartRevCTE  as PartRevRecursion
inner join Erp.PartMtl as PartMtl on 
	PartRevRecursion.PartRev_Company = PartMtl.Company
	and PartRevRecursion.Calculated_Child = PartMtl.PartNum
	and PartRevRecursion.PartRev_RevisionNum = PartMtl.RevisionNum)

select 
	[PartRevCTE].[PartRev_Company] as [PartRev_Company],
	[PartRevCTE].[Calculated_Parent] as [Calculated_Parent],
	[PartRevCTE].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	[PartRevCTE].[PartRev_AltMethod] as [PartRev_AltMethod],
	[PartRevCTE].[Calculated_Child] as [Calculated_Child],
	(((select top (1)  
	[LatestApprovedRev2].[RevisionNum] as [LatestApprovedRev2_RevisionNum]
from Erp.PartRev as LatestApprovedRev2
where (LatestApprovedRev2.Approved = true)
 and (LatestApprovedRev2.Company = PartRevCTE.PartRev_Company  and LatestApprovedRev2.PartNum = PartRevCTE.Calculated_Child)

order by LatestApprovedRev2.EffectiveDate Desc))) as [Calculated_ChildRevisionNum],
	[PartRevCTE].[Calculated_Level] as [Calculated_Level],
	(REPLACE(REPLACE(PartRevCTE.Calculated_Sort, '\\', '\'), '#', '')) as [Calculated_Hierarchy]
from  PartRevCTE  as PartRevCTE
order by Hierarchy
3

I have seen some funny data in the pegging files. in some cases I have found that i didn’t fully understand the way that the pegging worked. I would encourage you to pick one job/part and follow the pegging to understand how it is linked.

4

I think you need to add a condition here so it doesn’t pickup previously processed records. Maybe cte.DemandSeq < JH.DemandSeq

5

You never gave the recursive cte a termination condition. Move the where clause from the outer query into the second select of the cte (and change to Lvl + 1 < 3) and it should kick out of the loop after only a couple of level iterations rather than running forever and trying to filter after the fact.

WITH
	cte
	(
		Company
		, DemandSeq
		, DemandOrdNum
		, DmdPartNum
		, DemandDate
		, DemandQty
		, JMJobNum
		, MtlPartNum
		, Lvl
	)
AS
(
SELECT
	PDM.Company
	, PDM.DemandSeq
	, CAST(PDM.DemandOrdNum AS VARCHAR (20))
	, PDM.PartNum AS DmdPartNum
	, PDM.DemandDate
	, PDM.DemandQty
	, CAST(JH.JobNum AS VARCHAR (20)) AS JMJobNum
	, JH.PartNum AS MtlPartNum
	, 0
FROM
	Erp.PegDmdMst AS PDM
		INNER JOIN Erp.JobHead AS JH
			ON JH.Company = PDM.Company
			AND JH.JobNum = PDM.DemandOrdNum
WHERE
	PDM.PartNum = '<INSERT RELEVANT PARTNUM HERE>'

UNION ALL

SELECT
	PDM.Company
	, PDM.DemandSeq
	, CAST(PDM.DemandOrdNum AS VARCHAR (20))
	, PDM.PartNum AS DmdPartNum
	, PDM.DemandDate
	, PDM.DemandQty
	, CAST(JH.JobNum AS VARCHAR (20)) AS JMJobNum
	, JH.PartNum AS MtlPartNum
	, Lvl + 1
FROM
	Erp.PegDmdMst AS PDM
		INNER JOIN Erp.JobHead AS JH
			ON JH.Company = PDM.Company
			AND JH.JobNum = PDM.DemandOrdNum
		INNER JOIN cte
			ON cte.Company = JH.Company
			AND cte.DemandOrdNum = JH.JobNum
WHERE (Lvl + 1) < 3  -- Termination Check
)
SELECT DISTINCT
	Company
	, DemandSeq
	, DemandOrdNum
	, DmdPartNum
	, DemandDate
	, DemandQty
	, JMJobNum
	, MtlPartNum
	, Lvl
FROM
	cte
--WHERE
--	Lvl < 3
1 Like
6

Thanks, Jason. This appears to work well.