Recursive CTE Union BAQ

Kinetic ERP
1

Hi. I finally got my query working but now I have a sort of different problem. It works too well :melting_face: It shows me all the operation costs for all the jobs that go into the job number I enter (all our parts are made to stock with the lot number = the job they were made on so that’s the link I use to drill down) but I don’t get the information for the top level job I enter as the parameter. I feel like I need one more subquery but I can’t figure out how to enter it. Should it be an innersubquery or a CTE? How do I link it to the rest of the results. I am still a bit shaky on how this works.

/*
 * 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 [CTE] as 
(select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[PartTran].[LotNum] as [PartTran_LotNum],
	[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	[JobOper].[OpCode] as [JobOper_OpCode],
	[JobOper].[ActBurCost] as [JobOper_ActBurCost],
	[JobOper].[ActLabCost] as [JobOper_ActLabCost],
	[JobOper].[SubContract] as [JobOper_SubContract],
	(CAST(JobMtl.JobNum + ' -> ' + PartTran.LotNum AS NVARCHAR(MAX))) as [Calculated_Path]
from Erp.JobMtl as JobMtl
inner join Erp.PartTran as PartTran on 
	JobMtl.PartNum = PartTran.PartNum
	and JobMtl.JobNum = PartTran.JobNum
	and JobMtl.AssemblySeq = PartTran.AssemblySeq
	and ( PartTran.TranType = 'stk-mtl'  and PartTran.JobNum is not null )

inner join Erp.JobAsmbl as JobAsmbl on 
	PartTran.Company = JobAsmbl.Company
	and PartTran.LotNum = JobAsmbl.JobNum
inner join Erp.JobAsmbl as JobAsmbl2 on 
	JobMtl.Company = JobAsmbl2.Company
	and JobMtl.JobNum = JobAsmbl2.JobNum
	and JobMtl.AssemblySeq = JobAsmbl2.AssemblySeq
	and ( JobAsmbl2.JobNum = @JobNum  )

inner join Erp.JobOper as JobOper on 
	JobAsmbl2.Company = JobOper.Company
	and JobAsmbl2.JobNum = JobOper.JobNum
	and JobAsmbl2.AssemblySeq = JobOper.AssemblySeq
union all
select 
	[JobMtl1].[Company] as [JobMtl1_Company],
	[JobMtl1].[JobNum] as [JobMtl1_JobNum],
	[JobMtl1].[PartNum] as [JobMtl1_PartNum],
	[PartTran1].[LotNum] as [PartTran1_LotNum],
	[JobOper1].[AssemblySeq] as [JobOper1_AssemblySeq],
	[JobOper1].[OprSeq] as [JobOper1_OprSeq],
	[JobOper1].[OpCode] as [JobOper1_OpCode],
	[JobOper1].[ActBurCost] as [JobOper1_ActBurCost],
	[JobOper1].[ActLabCost] as [JobOper1_ActLabCost],
	[JobOper1].[SubContract] as [JobOper1_SubContract],
	(CTE.Calculated_Path + ' -> ' + JobMtl1.JobNum + ' -> ' + PartTran1.LotNum) as [Calculated_Path]
from Erp.JobMtl as JobMtl1
inner join Erp.PartTran as PartTran1 on 
	JobMtl1.PartNum = PartTran1.PartNum
	and JobMtl1.JobNum = PartTran1.JobNum
	and JobMtl1.AssemblySeq = PartTran1.AssemblySeq
	and ( PartTran1.TranType = 'stk-mtl'  and PartTran1.JobNum is not null )

inner join Erp.JobAsmbl as JobAsmbl1 on 
	PartTran1.Company = JobAsmbl1.Company
	and PartTran1.LotNum = JobAsmbl1.JobNum
	and PartTran1.AssemblySeq = JobAsmbl1.AssemblySeq
inner join Erp.JobOper as JobOper1 on 
	JobAsmbl1.Company = JobOper1.Company
	and JobAsmbl1.JobNum = JobOper1.JobNum
	and JobAsmbl1.AssemblySeq = JobOper1.AssemblySeq
inner join  CTE  as CTE on 
	JobMtl1.JobNum = CTE.PartTran_LotNum)

select 
	[CTE1].[JobMtl_Company] as [JobMtl_Company],
	[CTE1].[JobMtl_JobNum] as [JobMtl_JobNum],
	[CTE1].[JobMtl_PartNum] as [JobMtl_PartNum],
	[CTE1].[PartTran_LotNum] as [PartTran_LotNum],
	[CTE1].[JobOper_AssemblySeq] as [JobOper_AssemblySeq],
	[CTE1].[JobOper_OprSeq] as [JobOper_OprSeq],
	[CTE1].[JobOper_OpCode] as [JobOper_OpCode],
	[CTE1].[JobOper_ActBurCost] as [JobOper_ActBurCost],
	[CTE1].[JobOper_ActLabCost] as [JobOper_ActLabCost],
	[CTE1].[JobOper_SubContract] as [JobOper_SubContract],
	[CTE1].[Calculated_Path] as [Calculated_Path]
from  CTE  as CTE1
1 Like
2

Is this correct?

Not sure PartTran.LotNum is the JobNum. Maybe it’s needed in your case.

3

do you have an example of the desired output?

4

We use the job number a part was made on as the lot number to maintain some sort of link since all our parts are made to stock. This is what I get. In this example it’s missing the data from MRP000099574 (the parameter I entered)

image
image1540×566 16.5 KB

5

Ahh.

Are you able to get the costs of the Parent Part using the top query? The top query is the anchor part of the recursion. Looks like you created the top with the first child attached and then did a recursion on the child vs the parent.

1 Like
6

I am interested to learn how the Lot Tracking with the job number is working for you. We discussed this as a possibility at one time but ended up not implementing at that time. But its still definitely a need that we have as far as trying to track all the way down to the lower subassemblies.

Regarding your query, you may need to make another unionall after your top query that will bring in your top-level costs. I’m not exactly sure if it maps the same but I have done this to get an exploded MOM with operations and parts and to get the top level operations to show up I had to put it as a unionall to the top level query (because you don’t want to bring in the operations in your recursive part).

1 Like
7

Agreed.

CTE for Jobs only Parent \ Child levels

SubQuery for Operation Costs by Job

Join CTE to SubQuery

1 Like
8

I’ve decided to just make a separate BAQ to pull the information just for the top level job. It’s going on a dashboard with 3 other baqs all pulling information from the same job, so now I can subscribe those to the job number entered as the parameter and not have to enter the same job number 3 times as a parameter if each query.

It works great having the lot number be the job number. All our parts are made to stock then issued to an upper level job. With the lot=job we can find out what job the issued material was made on right from the top level job tracker screen.

1 Like