If you want to parameterize a selection of parent/children in the middle of the structure I would set up a parameter as a text field. Then I would have the users enter what they need in “this|kind|of|format”. Use a delimeter of your choice. Pipes are my go to because nobody uses those for anything. You could then parse this input and put into an ordered table.
In this example which was created from another BAQ developed inside our system, it works as long as I pick an Assembly further into the structure.
For Example,
JOBOPS_cteExample_HWM.baq (57.8 KB)
Assembly = 18.
But when I try to start at the parent assembly, the query times out and gives Bad SQL Statement, even though it runs fine for other assembly levels.
For Example, Assembly 0 ( the top level assembly in the structure ).
@jtownsend Epicor Support provided the SQL Error statement and evidently we have this hurdle:
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression ‘JobOpsCTE’.
So it looks like unless I could make a subquery that already does this grouping be the input into the CTE, I’m dead in the water again.
I had already created BAQ ( JOBOPS_cteExample ) where I instead will just return any operation based on a OprComplete parameter. However, the problem here is that it works fine when I use parameters that are “inside” the structure, such as any subassembly seq besides the root ( 0 ) will return expected results.
But if I try the parent level of the job as Assembly = 0, the query runs for 30 seconds and then:
Severity: Error, Text: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 30025.7936 ms.
Interestingly, since 2021.1.5 went in over the weekend, the Bad SQL Statement portion of the error seems to have gone away.
Any ideas how being Cloud hosted, how can I get some more time for the query to execute the parent level?
Not trying to jump in and muddy up the waters.
Not sure you need a CTE here.
You are looking for material parts on all open jobs and assemblies correct?
or just material parts on open jobs?
What is the OprComplete going to show?
@knash Hi, Ken. I am certainly not “sure” either.
What we have in place for the Operators now is a “Scheduling” Dashboard where the query filters out all completed Jobs and Job Operations. So it essentially returns a big list of all the open Operations for all subassemblies. Yes, they can filter by a specific Job number and often do. Additionally by grouping on the assigned Resource Group the Operator can see what work is still open at a particular machine, or start to frame some ideas about what RG is the bottleneck.
What I am trying to add, is an additional query so that they can see for a specific subassembly what work is left to be completed, but to do that I need to explode through the “child subassemblies” to show them specific Operations / RGs that they should go lean on to Complete so that the “parent” can get Completed.
If there are levels of subassemblies underneath, I need to explode through all of that and return the appropriate Operations. The CTE jumped out as the solution to recursion through a structured object like Materials, Operations, or Menu items. Alternatively, I considered a customization of the Job Traveler, to include Operation status, as this report already let’s you filter by Job and Assembly - and it does show children of that Assembly by default. But, I have been working on the CTE.
Yes, it is the basic requirement, that the output takes into account the parent/child relationship. They should be able to request output for Parent level 0, or any child subassembly sequence, and get the children of just that sequence.
Had to make a decision because of the Parent Level ( 0 ) problems with the BAQ, to just Filter those out using the Query options in the Dashboard. Also added Publish / Subscribe onto Dashboard. Now the end user can put there Tracker field value in there, bring in the “scheduling board” and then clicking on any record will show the “Children” on the Dashboard,
I am toying with the idea of going back into the BAQ ( lol ) and adding in Resource Group identification into the “Children” output.
But in a nutshell, this is what I was working my way through.
I think this ended up with 2 new Epicor Support Cases, the one unresolved as yet as to why the Parent Level 0 causes grief to the CTE.
I was able to create a CTE for the JobAsmbl. Which version of Epicor are you on? We are on 10.2.600.4 I can share the file if needed. The BAQ will return all subAsm for a job from a starting ASMB.
I don’t have good test data, so you may need to tweak this.
As for your other JobOper stuff I think that would all be subquery data. As you are just trying to describe the JobNum/Asm for each line.
/*
* 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 [JobAsmCTE] as
(select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
(0) as [Calculated_lv],
(CAST(JobAsmbl.Description as nvarchar(1000))) as [Calculated_Description],
(CAST('\' + JobAsmbl.JobNum as nvarchar(1000))) as [Calculated_Sort]
from Erp.JobAsmbl as JobAsmbl
where (JobAsmbl.JobNum = @JobNum and JobAsmbl.AssemblySeq = @AssemblySeq)
union all
select
[ChildJobAsmbl].[JobNum] as [ChildJobAsmbl_JobNum],
[ChildJobAsmbl].[AssemblySeq] as [ChildJobAsmbl_AssemblySeq],
(JobAsmCTE.Calculated_lv+1) as [Calculated_lv],
(CAST(REPLICATE ('| ' , JobAsmCTE.Calculated_lv) + ChildJobAsmbl.Description as nvarchar(1000))) as [Calculated_Desc],
(CAST(JobAsmCTE.Calculated_Sort + '\' + ChildJobAsmbl.Description as nvarchar(1000))) as [Calculated_Sort]
from Erp.JobAsmbl as ChildJobAsmbl
inner join JobAsmCTE as JobAsmCTE on
ChildJobAsmbl.Parent = JobAsmCTE.JobAsmbl_AssemblySeq
and ChildJobAsmbl.JobNum = JobAsmCTE.JobAsmbl_JobNum
where (ChildJobAsmbl.AssemblySeq > 0))
select
[MainJobAsmCTE].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
[MainJobAsmCTE].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[MainJobAsmCTE].[Calculated_lv] as [Calculated_lv],
[MainJobAsmCTE].[Calculated_Description] as [Calculated_Description],
[MainJobAsmCTE].[Calculated_Sort] as [Calculated_Sort]
from JobAsmCTE as MainJobAsmCTEI just made a few changes to show more like the grid you just posted.
/*
* 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 [JobAsmCTE] as
(select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Parent] as [JobAsmbl_Parent],
(0) as [Calculated_lv],
(CAST(JobAsmbl.Description as nvarchar(1000))) as [Calculated_Description],
(CAST('\' + JobAsmbl.JobNum as nvarchar(1000))) as [Calculated_Sort]
from Erp.JobAsmbl as JobAsmbl
where (JobAsmbl.JobNum = @JobNum and JobAsmbl.AssemblySeq = @AssemblySeq)
union all
select
[ChildJobAsmbl].[JobNum] as [ChildJobAsmbl_JobNum],
[ChildJobAsmbl].[AssemblySeq] as [ChildJobAsmbl_AssemblySeq],
[ChildJobAsmbl].[PartNum] as [ChildJobAsmbl_PartNum],
[ChildJobAsmbl].[Parent] as [ChildJobAsmbl_Parent],
(JobAsmCTE.Calculated_lv+1) as [Calculated_lv],
(CAST(REPLICATE ('| ' , JobAsmCTE.Calculated_lv) + ChildJobAsmbl.Description as nvarchar(1000))) as [Calculated_Desc],
(CAST(JobAsmCTE.Calculated_Sort + '\' + ChildJobAsmbl.Description as nvarchar(1000))) as [Calculated_Sort]
from Erp.JobAsmbl as ChildJobAsmbl
inner join JobAsmCTE as JobAsmCTE on
ChildJobAsmbl.Parent = JobAsmCTE.JobAsmbl_AssemblySeq
and ChildJobAsmbl.JobNum = JobAsmCTE.JobAsmbl_JobNum
where (ChildJobAsmbl.AssemblySeq > 0))
select
[MainJobAsmCTE].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
[MainJobAsmCTE].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[MainJobAsmCTE].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[MainJobAsmCTE].[JobAsmbl_Parent] as [JobAsmbl_Parent],
[MainJobAsmCTE].[Calculated_lv] as [Calculated_lv],
[MainJobAsmCTE].[Calculated_Description] as [Calculated_Description],
[MainJobAsmCTE].[Calculated_Sort] as [Calculated_Sort]
from JobAsmCTE as MainJobAsmCTEI was researching last night, WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Learn
and one important thing caught my eye…
In the Guidelines for Defining and Using Recursive Common Table Expressions section,
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100.
So that answers part of the other anomaly that we saw in the original Case where the same records were repeated 100 times. The query in that one is in a loop and gives you 100 times then quits. But that is seemingly a problem with the documentation and the steps in that specific example. But it always made me wonder why in this Case with BAQ ( JOBOPS_cteExample_HWM ) I did not see the same behavior.
So, what I will do is, I will go to the “recursive” subquery which is “JobOpsRecursion” and I will add to the Subquery Criteria, that the JobAsmbl1.AssemblySeq <> 0. This should eliminate the “recursion” from pulling in the Parent Level 0 record. At first, I did not understand why it would be an issue for only the Parent Level 0 and not any others, because the parent of zero should not be also “0” but alas, in Epicor, it actually is. The actual Parent value in JobAsmbl for the AssemblySeq of 0, is also 0.
A Rooky mistake.
@knash Thank you so much for your post. It triggered me to go look more closely at Microsoft’s documentation on the CTE. Upon closer inspection, I know that I will see that you must, and in fact are, avoiding the issue that I encountered by using the Where > 0 in your “recursive” subquery. You are genius.
This is probably a good learning lesson for a lot of new folks!
Nice!!! What seemed liked a brilliant move is just experience helping out, nothing more.
I see the CTE as two datasets.
The Anchor and the Recursive call. You cannot have the Anchor record in the recursive dataset else you will have the endless loop issue you mentioned.
Your case of subassemblies of subassemblies made this fun!! Let us know how this ends up.
3 Likes
Worked out fantastic. The query now adds to a “scheduling board” in “publish/subscribe” fashion so that a “scheduler” can select a record in the output, and see the “child” assemblies that are not yet completed, and I added some hours info for icing.
1 Like
I have stumbled upon this with a similar issue. I have a recursive query setup to make an exploded BOM and do some cost calculations that Epicor doesn’t do well by default. However, I am stuck trying to grab the most recent revision of the “children”. I must return exactly 1 revision and currently, it’s possible to get multiple revisions back by filtering alone. Ideally, I’d do a subquery and use an aggregate to get the MAX revision number. However, It yells at me when I stick that subquery in the “Union” part of the BAQ. Any ideas?
In case it helps, this is my BAQ:
with [SubQuery1] as
(select
[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],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(0) as [Calculated_Hierarchy],
(case
when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar(25))
else cast(PartMtl.MtlSeq as nvarchar(25))
end) as [Calculated_Ind1],
[PartMtl].[Company] as [PartMtl_Company],
[PartRev2].[Plant] as [PartRev2_Plant],
(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
and PartMtl.RevisionNum = PartRev2.RevisionNum
and PartMtl.AltMethod = PartRev2.AltMethod
and ( PartRev2.AltMethod = '' )
inner join (select
[PartRev3].[Company] as [PartRev3_Company],
[PartRev3].[PartNum] as [PartRev3_PartNum],
(max(PartRev3.RevisionNum)) as [Calculated_MaxRev]
from Erp.PartRev as PartRev3
where (PartRev3.Approved = true)
group by [PartRev3].[Company],
[PartRev3].[PartNum]) as GetMaxRev on
PartRev2.Company = GetMaxRev.PartRev3_Company
and PartRev2.PartNum = GetMaxRev.PartRev3_PartNum
and PartRev2.RevisionNum = GetMaxRev.Calculated_MaxRev
where PartMtl.PartNum = @PartNum
union all
select
[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],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(case
when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
else cast(PartMtl1.MtlSeq as nvarchar(25))
end) as [Calculated_Ind2],
[PartMtl1].[Company] as [PartMtl1_Company],
[PartRev].[Plant] as [PartRev_Plant],
(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 PartMtl1.AltMethod = PartRev.AltMethod
and ( PartRev.AltMethod = '' and PartRev.Approved = true ))
select
[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[PartRev2_Plant] as [PartRev2_Plant],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[ClassID] as [Part_ClassID],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(SubQuery11.Calculated_ParentQty * SubQuery11.PartMtl_QtyPer) as [Calculated_ExtQty],
[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
(case
when PartPlant.SourceType <> 'M'
then PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.StdLaborCost
else 0
end) as [Calculated_StdCost],
(ExtQty * StdCost) as [Calculated_ExtStdCost],
(case
when PartPlant.SourceType <> 'M'
then PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost + PartCost.LastMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.LastLaborCost
else 0
end) as [Calculated_LastCost],
(ExtQty * LastCost) as [Calculated_ExtLastCost],
(case
when PartPlant.SourceType <> 'M'
then PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost
when PartPlant.SourceType = 'M' and PartPlant.PhantomBOM = 0
then PartCost.AvgLaborCost
else 0
end) as [Calculated_AvgCost],
(ExtQty * AvgCost) as [Calculated_ExtAvgCost],
[Vendor].[Name] as [Vendor_Name],
[PartClass].[BuyerID] as [PartClass_BuyerID],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
[PartPlant].[MfgLotMultiple] as [PartPlant_MfgLotMultiple],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
(case
when StdCost <> 0
then abs((LastCost - StdCost) / StdCost)
else 0
end) as [Calculated_LastCostVar],
(case
when StdCost <> 0
then abs((AvgCost - StdCost) / StdCost)
else 0
end) as [Calculated_AvgCostVar],
[CostUpdateRelevance].[Calculated_DaysSinceUpdate] as [Calculated_DaysSinceUpdate],
[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
left outer join Erp.Part as Part on
SubQuery11.PartMtl_Company = Part.Company
and SubQuery11.PartMtl_MtlPartNum = Part.PartNum
left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
left outer join Erp.PartPlant as PartPlant on
SubQuery11.PartMtl_Company = PartPlant.Company
and SubQuery11.PartRev2_Plant = PartPlant.Plant
and SubQuery11.PartMtl_MtlPartNum = PartPlant.PartNum
left outer join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(datediff(day, max(PartTran.TranDate), Constants.Today)) as [Calculated_DaysSinceUpdate]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'ADJ-CST')
group by [PartTran].[Company],
[PartTran].[PartNum]) as CostUpdateRelevance on
SubQuery11.PartMtl_MtlPartNum = CostUpdateRelevance.PartTran_PartNum
order by SubQuery11.Calculated_Ind1
Interesting!
I have a full plate for most of today, but I have read your post and surely do intend to load your BAQ and see what happens just as soon as I can. Are you able to or did you also attach the file as that would make testing it super easy?
DMR_ProjectedCosts.baq (166.7 KB)
Here it is. You will find that the last subquery (GetMaxRev) is the one that I am trying to use in the Union subquery (Subquery7). It gives me the error. So I’ve just resorted to looking for PartRev.Approved = true on the selected child BOM. So my failure occurred when adding the GetMaxRev subquery to that Subquery7.
Any ideas?
I don’t have a thing yet.
One thing that sticks out is in the “recursive” or UnionAll subquery, this is where you have to include some logic that will prevent duplicate records from the CTE subquery, from populating in those results. To do this, use the SubQuery Criteria tab. You’ll see this discussed above as “eliminating the Parent Assembly from the recursion result set by adding criteria there as AssemblySeq > 0”.
Subsequently, I believe that the CTE subquery could use the SubQuery Criteria as well, to control the results as you desire, as opposed to the Table Criteria that you have setup in there now.
I got it now. This BOM CTE the filtering is on the table joins, sending the material part from the “parent” ( from the parameter as well ) back as the part.
What I found in our environment was that seemingly duplicate records were being returned, but that was because the PartCost table carries multiple records based on Plant as shown by exposing the CostID into the Display Fields.
1 Like
Ok, there is potential conflict here Dan in that Epicor looks at the Effective Date / Time stamp along with the Approval flag and Current Plant to determine the “Default” revision.
If I take an example Part and just start creating new revisions for him, for multiple Sites, and approve them all, and then I go create a new Line for an Order for this Part - Epicor will pick the most recently Effective and Approved revision for my current Site ( Plant ). But, we also need to be aware that an engineering process may “forward date” the effective date.
In my mind, you would want to reproduce this functionality in your query, not simply taking the highest revision number. I suppose in your specific business process it may make sense to not jump to this level, but all this just to explain that this is how Epicor handles revisions.