Recursive Job Assembly Operation List

NateS · August 1, 2023, 6:41pm

I am trying to get a CTE recursive BAQ to work. I would like to see all the operations for all the assemblies for a job number. I built this BAQ following the help file, and I think I got it mostly right. It is returning results, but they don’t appear exactly as I imagined.

I admit I am no good at CTEs at all! There is a solid chance I mucked this up someplace. Can you see anything wrong?
OpListForFixtures.baq (55.2 KB)

I have one job with 97 assemblies! The op list looks good for all the asms except for the last one #97. it lists all the ops, and then lists op 900 over and over again, once for each ASM level.

Banderson · August 1, 2023, 6:50pm

You don’t need to do a recursive BAQ for the job tables. All of the operations are in the JobOP table.

NateS · August 1, 2023, 6:52pm

I think I need to see how the assemblies are related. So if there is a sub of a sub of a sub, I want to see that. I know I can see the list of all assemblies, but I can’t see how they are related. This is also just to help me understand CTEs, as I have never made one that did me any good.

Banderson · August 1, 2023, 6:59pm

This level is going to bring in the whole job, all of the operations. Try running just that level (change it to top level, and make all of the other levels inner subs) and you will probably see the first part of the results that looks “Good”. You have to limit this to only assembly level 0 so that you can expand from there.

Banderson · August 1, 2023, 7:02pm

Also, you need to relate to a level up or down somehow. You are just joining the CTE back to the same fields, so it’s just going to continuously loop. You need to get to assembly seq and parent so that you can walk up the hierarchy.

NateS · August 2, 2023, 2:05pm

I have been playing around with Asm Seq, Parent, and Child trying to get this BAQ to return a nested list of assemblies and their operations. Do I have to use the parent or asm seq fields in the Hierarchy expression?

Banderson · August 2, 2023, 2:46pm

Let’s break this down into smaller chunks first. And verify that is makes sense what you are trying to do.

First, I think that your hierarchical part of the query can’t have the operations in it. Why? Because each assembly sequence can have more than one operation. A hierarchical query will add more records per row. So if you start adding duplicate rows, you hierarchy is going to be multiplied. So what you probably need to do, is make a query that just has the assembly sequences and part numbers for the assemblies in it. Then once you have that list (which will be your union all query) you can encapsulate that in parenthesis, and join that to the job operation table and find all of the operations based on the job number and asm seq fields.

So your union all is going to look like this (with other fields added as needed, like job number, and parent, part number for info, etc)

jobNum	asm	level	indent	parent
xxx-x-x	0	1	0	0 (note, this is 0 because you can’t have a null int, but is really nothing)
xxx-x-x	10	2	0…10	0
xxx-x-x	20	2	0…20	0
xxx-x-x	30	2	0…30	0
xxx-x-x	40	3	0…10…40	10
xxx-x-x	50	3	0…10…50	10
xxx-x-x	60	3	0…10…60	10
xxx-x-x	70	3	0…20…70	20
xxx-x-x	80	3	0…20…80	20
xxx-x-x	90	3	0…20…90	20
xxx-x-x	100	3	0…30…100	30

Then once you have that, you simply join to the job operation table with JobNum and Asm Seq.

So just start with the CTE as a top level and you should get this (and only this)

jobNum	asm	level	indent	parent
xxx-x-x	0	1	0	0

Then when you add the heirarchy, you should get the rest of the rows for the job. like shown above.

Once you have that working correctly, THEN add the operation information at the top level.

NateS · August 10, 2023, 6:04pm

Dang these cursed iterative CTEs!

I did like you said and started simple, building step by step. I think I have the outline for a BAQ that should work, however I keep getting Syntax OK, and Bad SQL Statement.

with [JobsCTE] 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_Hierarchy],
	(cast(substring('...',1,(Hierarchy +1) ) + JobAsmbl.Parent as nvarchar(25))) as [Calculated_Ind1]
from Erp.JobAsmbl as JobAsmbl
where (JobAsmbl.JobNum = '32030/1'  and JobAsmbl.AssemblySeq = 0)
union all
select 
	[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum],
	[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
	[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
	[JobsCTE1].[JobAsmbl_Parent] as [JobAsmbl_Parent],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(cast(substring('......',1,(Hierarchy2 +1) ) + JobsCTE1.JobAsmbl_Parent as nvarchar(25))) as [Calculated_Ind2]
from Erp.JobAsmbl as JobAsmbl1
inner join  JobsCTE  as JobsCTE1 on 
	JobAsmbl1.JobNum = JobsCTE1.JobAsmbl_JobNum
	and JobAsmbl1.Parent = JobsCTE1.JobAsmbl_AssemblySeq)

select 
	[JobsCTE].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
	[JobsCTE].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobsCTE].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
	[JobsCTE].[JobAsmbl_Parent] as [JobAsmbl_Parent],
	[JobsCTE].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[JobsCTE].[Calculated_Ind1] as [Calculated_Ind1],
	[JobOper].[OprSeq] as [JobOper_OprSeq]
from  JobsCTE  as JobsCTE
inner join Erp.JobOper as JobOper on 
	JobsCTE.JobAsmbl_JobNum = JobOper.JobNum
	and JobsCTE.JobAsmbl_AssemblySeq = JobOper.AssemblySeq

This is how I understand it.

Subquery 1 - JobsCTE. This is a CTE subquery that returns all the parts in JobAsm where JobNum = user input. This contains the Parent field which shows the level of the parent asm.

Subquery 2 - JobsUnion. This is a UnionAll subquery that links JobAsm with the CTE table. It links on JobAsmbl.JobNum=JobsCTE.JobNum and JobAsmbl.AsmSeq = JobsCTE.Parent.

TopLevel Query 3 - The Top Level. This just returns everything from JobsCTE, and links up the operation table to try to pull in the ops for the Asms that get returned.

Am I missing something obvious? I have tried various iterations using JobAsm.Parent, JobASM.AsmSeq, JobAsm.PartNum. But all iterations gave me the same results of Syntax ok, Bad SQL.

'I also tried this:
inner join  JobsCTE  as JobsCTE1 on 
	JobAsmbl1.JobNum = JobsCTE1.JobAsmbl_JobNum
	and JobAsmbl1.AssemblySeq = JobsCTE1.Parent)

'and tried this:
inner join  JobsCTE  as JobsCTE1 on 
	JobAsmbl1.JobNum = JobsCTE1.JobAsmbl_JobNum
	and JobAsmbl1.PartNum = JobsCTE1.JobAsmbl_PartNum)
'(I think I got some results using this approach but it wasn't right.)

Banderson · August 10, 2023, 6:35pm

Do you actually have /s in your job numbers? Out of curiosity, can you make a job without that in there and see if it works with that job?

knash · August 10, 2023, 6:46pm

What does the data look like? Parent returns 0 for me. Having a hard time visualizing.

A CTE of the Parent of 1 will show all three records.

Parent		Child
1		2
2		3
3		4

Are you able to to share a few records.

Banderson · August 10, 2023, 8:30pm

I found it. You can’t have the assembly seq 0 in the union. Since the parent of 0 is 0 (because it’s can’t be blank) it’s looping on itself. Just filter the job table on the union to not include assembly seq 0

Here’s some screen shots of working, and not. (I don’t have any multi levels… so it’s not really gonna loop much my system.)

NateS · August 11, 2023, 1:15pm

In your example, you removed the ‘Ind’ and ‘Hierarchy’ fields. That got rid of my error, and let the BAQ run. But the results don’t have any nesting. For this job I have three levels of ASMs.
parttree

The BAQ without Heirarchy returns these results:

Summary

Job Number	Asm	Part	Operation
MyJob	0	ASM0	10
MyJob	0	ASM0	20
MyJob	0	ASM0	900
MyJob	1	ASM1	10
MyJob	1	ASM1	20
MyJob	1	ASM1	900
MyJob	2	ASM2	10
MyJob	2	ASM2	20
MyJob	2	ASM2	30
MyJob	2	ASM2	40
MyJob	2	ASM2	50
MyJob	2	ASM2	60
MyJob	2	ASM2	65
MyJob	2	ASM2	70
MyJob	2	ASM2	90
MyJob	2	ASM2	100
MyJob	2	ASM2	110
MyJob	2	ASM2	120
MyJob	2	ASM2	130
MyJob	2	ASM2	140
MyJob	2	ASM2	150
MyJob	2	ASM2	160
MyJob	2	ASM2	170
MyJob	2	ASM2	180
MyJob	2	ASM2	190
MyJob	2	ASM2	200
MyJob	2	ASM2	220
MyJob	2	ASM2	230
MyJob	2	ASM2	900
MyJob	3	ASM3	10
MyJob	3	ASM3	20
MyJob	3	ASM3	30
MyJob	3	ASM3	40
MyJob	3	ASM3	50
MyJob	3	ASM3	60
MyJob	3	ASM3	65
MyJob	3	ASM3	70
MyJob	3	ASM3	90
MyJob	3	ASM3	100
MyJob	3	ASM3	110
MyJob	3	ASM3	120
MyJob	3	ASM3	130
MyJob	3	ASM3	140
MyJob	3	ASM3	150
MyJob	3	ASM3	160
MyJob	3	ASM3	170
MyJob	3	ASM3	180
MyJob	3	ASM3	190
MyJob	3	ASM3	200
MyJob	3	ASM3	220
MyJob	3	ASM3	230
MyJob	3	ASM3	900
MyJob	2	ASM2	80
MyJob	2	ASM2	210
MyJob	3	ASM3	80
MyJob	3	ASM3	210

Part/job numbers aren’t important to this, so I replaced them for clarity.

 
with [JobsCTE] as 
(select 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobAsmbl].[Parent] as [JobAsmbl_Parent]
from Erp.JobAsmbl as JobAsmbl
where (JobAsmbl.JobNum = 'MyJob'  and JobAsmbl.AssemblySeq = 0)
union all
select 
	[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum],
	[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
	[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
	[JobsCTE1].[JobAsmbl_Parent] as [JobAsmbl_Parent]
from Erp.JobAsmbl as JobAsmbl1
inner join  JobsCTE  as JobsCTE1 on 
	JobAsmbl1.JobNum = JobsCTE1.JobAsmbl_JobNum
	and JobAsmbl1.Parent = JobsCTE1.JobAsmbl_AssemblySeq
	and JobAsmbl1.AssemblySeq <> 0)

select 
	[JobsCTE].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
	[JobsCTE].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobsCTE].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
	[JobsCTE].[JobAsmbl_Parent] as [JobAsmbl_Parent],
	[JobOper].[OprSeq] as [JobOper_OprSeq]
from  JobsCTE  as JobsCTE
inner join Erp.JobOper as JobOper on 
	JobsCTE.JobAsmbl_JobNum = JobOper.JobNum
	and JobsCTE.JobAsmbl_AssemblySeq = JobOper.AssemblySeq

knash · August 11, 2023, 2:36pm

I am not seeing in the data how ASM3 is the Child of ASM1; as the Parent is 0.

NateS · August 11, 2023, 2:38pm

Yeah that parent column isn’t accurate. It is just returning 0 all the way regardless. The image showing the tree view is accurate.

knash · August 11, 2023, 2:43pm

Think you want to check out the PriorPeer and/or NextPeer field(s).

NateS · August 14, 2023, 5:17pm

I am back at this again trying my darndest to understand recursive CTEs.

In all the examples a recursive CTE BAQ has three subqueries.

The CTE is first.
The Union All is second.
The TopLevel is third.

The top level just spits out the data from the CTE. But where does the UnionAll get included? I don’t use the UnionAll in my top level, and it isn’t referenced by the CTE. I guess I am still missing some critical information on how this works.

jkane · August 14, 2023, 6:11pm

Would seeing a SQL example help?

NateS · August 14, 2023, 6:16pm

I have several examples open from around epiusers. The thing that seems in common are the three things I posted above. I understand a TopLevel is where the results come from, and the CTE is the base. But how is the UnionAll query pulled in? Shouldn’t I have to reference that unionAll in my top level to get any of that data in my results?

jkane · August 14, 2023, 6:24pm

No, the union all is unioning the CTE and itself. See if this helps you understand.

1 is the CTE
2 is the Union All
3 is the Top Level

The Union All has to have the CTE in it. You add the base table (from the CTE) as Table 1 and join that to the CTE which will be Table 2. Then in the Top Level, you bring in the Union All query as the results.

NateS · August 14, 2023, 6:30pm

In the top level I can’t add the UnionAll as a source, only the CTE is available.

Topic		Replies	Views
~If you want to recursively traverse the BOM you may need a CTE query?~ Kinetic ERP baq	43	10610	November 1, 2021
Recursive CTE BAQ Set-up Help Kinetic ERP baq	30	1075	February 1, 2024
BAQ calculated field to show part number of parent assembly Epicor ERP 10	13	3828	March 17, 2017
BAQ for Operations to Work on Kinetic ERP epicor , baq , dashboard	7	619	May 2, 2024
I Still Don't Understand CTEs Kinetic ERP e10 , cte	10	1370	September 14, 2022

Recursive Job Assembly Operation List

Related Topics