Recursive CTE BAQ Set-up Help

Kinetic ERP
1

Anybody have the time/patience to help with yet another CTE BAQ set up?

This is my first attempt and I think I’m just struggling to grasp the concepts/structure of setting up the CTE and/or UnionAll queries.

I’ve uploaded a ton of historical job cost information into a UD table. I’m now trying to query this data to compile the job cost to match the costs displayed in the legacy system.

I’m using several CTE queries to sum various data (Labor, Materials, Subcontract, etc.) WHERE JobNum and AssyNum are the same… and then bringing that into my top level. No problem there for the TOP LEVEL Job Cost (main job assembly).

What I can’t figure out is how to make the query recursive to capture the costs of Lower Level Assemblies. (making the connection between parent/child assemblies)

This is my TOP LEVEL:

image
image803×416 40.8 KB

CTE Query of Subassemblies:
image

I am NOT using a UNION ALL query (yet?)… which I’ve seen a ton of reference to in other threads, but again, I can’t grasp how that needs to be set-up.

I don’t think this is anybody’s favorite subject, but if anyone has some spare time to help me figure out how to set-up the recursive parent/child portion, I would greatly appreciate it. My brain is mush right about now from thinking in circles and not getting anywhere.

2

You have to do the Union All to do the recursion, otherwise it will just be a single level join.

3

Yeah, I’m digging into that right now (scrubbing old posts for tid-bits).

Where do I setup the recursion… in the SubQuery Criteria?

4

I would do your BAQ this way.

SubQuery 1: Asm 0 Costs (CTE)
SubQuery 2: Subassembly Costs (CTE)
SubQuery 3: Union All
SubQuery 4: Top Level

SubQuery 3 you will bring in SQ 2 as table 1 and SQ 1 as table 2. Join on SQ 2 SubAsm PartNum to SQ1 PartNum.

Last you will create the Top Level query and bring in SQ 3 as the table.

When dealing with complex queries and recursion, I like to make the complex queries into CTE so I am only dealing with 2 tables to do the recursion. Makes everything simpler.

2 Likes
5

Okay, I feel like an idiot. How do I bring SQ3 into my Top query? A UnionAll query doesn’t show in the SubQueries list.

6

Oh shoot!! You are not an idiot, I messed up. I’m trying to do this from memory.

SubQuery 1 should be the Subassembly
SubQuery 2 should be the Asm 0

SubQuery 3 you should bring in SQ 1 as the first table and SQ 2 as the second.

SubQuery 4 you want to bring in the results from SQ 2.

Sorry about that.

7

Or, if you want to cut out a CTE.

SQ 1 would be what you labeled as Top Level in your original. (CTE)

SQ 2 would be your subassembly query, then you would add in SQ 1 and join it to the UD table. (Union All)

SQ 3 would then be bringing in SQ 1. (Top Level)

8

Subquery criteria (the WHERE statement) in the query following UNION ALL is where you’d set a recursion limit, if you can’t count on a join running out of hierarchy to recurse through.

The join in the query following UNION ALL to the query preceding UNION ALL is something like on queryB.index = queryA.index + 1 or whatever the hierarchy is.

I don’t know if you’re recursing through a list of material or operation or assembly sequences, but if you are it helps to have a subquery that applies a row_number() over(partition by <whatever> order by sequence <asc or desc>), then you can say +1 or -1 in your join regardless of what the sequence number gaps are.

1 Like
9

Ooo… I think I may have got it. My join was miffed.

I was having trouble with my UnionAll join because I was trying to cast a “key” (string) to a decimal. I had to do Table1.Number03 “Like” cast(Table2.Key2 as decimal) and that seems to have worked.

10

Choosing a part to test against, I picked one that is generally always made as the top assembly and typically never has any subassemblies.

The only job where it DID have a subassembly is the second highlighted row below, and the subassembly costs are all correct.

The ODD (first highlighted row) I can’t figure out. In this case, the part was being produced AS a subassembly (not the top level part). It had no children… But for some reason a crazy amount of lower-level cost is showing up. Not sure what’s going on there.

image
image854×195 11.5 KB

11

yeah, my recursion isn’t working correctly.

I ran my query against a specific job number… the Assemblies themselves are coming across with the correct values… but the recursion is not working on the subassemblies. They’re all coming across with the same values.

image
image1516×251 12.3 KB

Looks like my recursion join, or a table relationship is botched somewhere.

12

What is your join on the Union All?

13

image
image1071×91 8.11 KB

Key1 is the JobNum in both cases.

14

Can you post the SQL?

15

Its pretty ugly…

/*
 * 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 [Material] as 
(select 
	[UD03_Material].[Company] as [UD03_Material_Company],
	[UD03_Material].[Key1] as [UD03_Material_Key1],
	[UD03_Material].[Key2] as [UD03_Material_Key2],
	(isnull(sum(
 isnull(UD03_Material.Number10,0)*
 isnull(UD03_Material.Number11,0)),0)) as [Calculated_MaterialSum],
	(isnull(sum(
 isnull(UD03_Material.Number10,0)*
 isnull(UD03_Material.Number11,0)*
 isnull(UD03_Material.Number12,0)),0)) as [Calculated_MtlBurSum]
from Ice.UD03 as UD03_Material
where (UD03_Material.ShortChar01 = 'Matl')
group by [UD03_Material].[Company],
	[UD03_Material].[Key1],
	[UD03_Material].[Key2])
 ,[Labor] as 
(select 
	[Labor].[Company] as [Labor_Company],
	[Labor].[Key1] as [Labor_Key1],
	[Labor].[Key2] as [Labor_Key2],
	(isnull(sum(
 isnull(Labor.Number14,0)*
 isnull(Labor.Number16,0)*
 (1+isnull(Labor.Number18,0))),0)) as [Calculated_LaborCost],
	(isnull(sum(
 isnull(Labor.Number15,0)*
 isnull(Labor.Number17,0)),0)) as [Calculated_BurdenCost]
from Ice.UD03 as Labor
where (Labor.ShortChar01 = 'Labor')
group by [Labor].[Company],
	[Labor].[Key1],
	[Labor].[Key2])
 ,[SubContract] as 
(select 
	[SubContract].[Company] as [SubContract_Company],
	[SubContract].[Key1] as [SubContract_Key1],
	[SubContract].[Key2] as [SubContract_Key2],
	(isnull(sum(
 isnull(SubContract.Number05,0)),0)) as [Calculated_SubContractSum]
from Ice.UD03 as SubContract
where (SubContract.ShortChar01 = 'Sub')
group by [SubContract].[Company],
	[SubContract].[Key1],
	[SubContract].[Key2])
 ,[Misc] as 
(select 
	[Misc].[Company] as [Misc_Company],
	[Misc].[Key1] as [Misc_Key1],
	[Misc].[Key2] as [Misc_Key2],
	(isnull(sum(
 isnull(Misc.Number05,0)),0)) as [Calculated_MiscSum]
from Ice.UD03 as Misc
where (Misc.ShortChar01 = 'Misc')
group by [Misc].[Company],
	[Misc].[Key1],
	[Misc].[Key2])
 ,[TopLevel] as 
(select 
	[TopLevelAssy].[Key1] as [TopLevelAssy_Key1],
	[TopLevelAssy].[Key2] as [TopLevelAssy_Key2],
	[TopLevelAssy].[Number03] as [TopLevelAssy_Number03],
	[Material1].[Calculated_MaterialSum] as [Calculated_MaterialSum],
	[Material1].[Calculated_MtlBurSum] as [Calculated_MtlBurSum],
	[Labor2].[Calculated_LaborCost] as [Calculated_LaborCost],
	[Labor2].[Calculated_BurdenCost] as [Calculated_BurdenCost],
	(isnull(sum(isnull(SubContract2.Calculated_SubContractSum,0))+
 sum(isnull(Misc2.Calculated_MiscSum,0)),0)) as [Calculated_TopLevelSubMisc]
from Ice.UD03 as TopLevelAssy
left outer join  Material  as Material1 on 
	TopLevelAssy.Key1 = Material1.UD03_Material_Key1
	and TopLevelAssy.Key2 = Material1.UD03_Material_Key2
left outer join  Labor  as Labor2 on 
	TopLevelAssy.Key1 = Labor2.Labor_Key1
	and TopLevelAssy.Key2 = Labor2.Labor_Key2
left outer join  SubContract  as SubContract2 on 
	TopLevelAssy.Key1 = SubContract2.SubContract_Key1
	and TopLevelAssy.Key2 = SubContract2.SubContract_Key2
left outer join  Misc  as Misc2 on 
	TopLevelAssy.Key1 = Misc2.Misc_Key1
	and TopLevelAssy.Key2 = Misc2.Misc_Key2
where (TopLevelAssy.ShortChar01 = 'Header')
group by [TopLevelAssy].[Key1],
	[TopLevelAssy].[Key2],
	[TopLevelAssy].[Number03],
	[Material1].[Calculated_MaterialSum],
	[Material1].[Calculated_MtlBurSum],
	[Labor2].[Calculated_LaborCost],
	[Labor2].[Calculated_BurdenCost])
 ,[AssyMatl] as 
(select 
	[UD03_AssyMatl].[Company] as [UD03_AssyMatl_Company],
	[UD03_AssyMatl].[Key1] as [UD03_AssyMatl_Key1],
	[UD03_AssyMatl].[Key2] as [UD03_AssyMatl_Key2],
	(isnull(sum(
 isnull(UD03_AssyMatl.Number10,0)*
 isnull(UD03_AssyMatl.Number11,0)),0)) as [Calculated_AssyMatlSum],
	(isnull(sum(
 isnull(UD03_AssyMatl.Number10,0)*
 isnull(UD03_AssyMatl.Number11,0)*
 isnull(UD03_AssyMatl.Number12,0)),0)) as [Calculated_AssyMatlBurSum]
from Ice.UD03 as UD03_AssyMatl
where (UD03_AssyMatl.ShortChar01 = 'Matl')
group by [UD03_AssyMatl].[Company],
	[UD03_AssyMatl].[Key1],
	[UD03_AssyMatl].[Key2])
 ,[AssyLabor] as 
(select 
	[UD03_Assy_Labor].[Company] as [UD03_Assy_Labor_Company],
	[UD03_Assy_Labor].[Key1] as [UD03_Assy_Labor_Key1],
	[UD03_Assy_Labor].[Key2] as [UD03_Assy_Labor_Key2],
	(isnull(sum(
 isnull(UD03_Assy_Labor.Number14,0)*
 isnull(UD03_Assy_Labor.Number16,0)*
 (1+isnull(UD03_Assy_Labor.Number18,0))),0)) as [Calculated_AssyLabor],
	(isnull(sum(
 isnull(UD03_Assy_Labor.Number15,0)*
 isnull(UD03_Assy_Labor.Number17,0)),0)) as [Calculated_AssyBurden]
from Ice.UD03 as UD03_Assy_Labor
where (UD03_Assy_Labor.ShortChar01 = 'Labor')
group by [UD03_Assy_Labor].[Company],
	[UD03_Assy_Labor].[Key1],
	[UD03_Assy_Labor].[Key2])
 ,[AssySubContract] as 
(select 
	[UD03_AssySub].[Company] as [UD03_AssySub_Company],
	[UD03_AssySub].[Key1] as [UD03_AssySub_Key1],
	[UD03_AssySub].[Key2] as [UD03_AssySub_Key2],
	(isnull(sum(
 isnull(UD03_AssySub.Number05,0)),0)) as [Calculated_AssySubSum]
from Ice.UD03 as UD03_AssySub
where (UD03_AssySub.ShortChar01 = 'Sub')
group by [UD03_AssySub].[Company],
	[UD03_AssySub].[Key1],
	[UD03_AssySub].[Key2])
 ,[AssyMisc] as 
(select 
	[UD03_AssyMisc].[Company] as [UD03_AssyMisc_Company],
	[UD03_AssyMisc].[Key1] as [UD03_AssyMisc_Key1],
	[UD03_AssyMisc].[Key2] as [UD03_AssyMisc_Key2],
	(isnull(sum(
 isnull(UD03_AssyMisc.Number05,0)),0)) as [Calculated_AssyMiscSum]
from Ice.UD03 as UD03_AssyMisc
where (UD03_AssyMisc.ShortChar01 = 'Misc')
group by [UD03_AssyMisc].[Company],
	[UD03_AssyMisc].[Key1],
	[UD03_AssyMisc].[Key2])
 ,[Assembly] as 
(select 
	[UD03_Assy].[Company] as [UD03_Assy_Company],
	[UD03_Assy].[Key1] as [UD03_Assy_Key1],
	[UD03_Assy].[Key2] as [UD03_Assy_Key2],
	[UD03_Assy].[Number02] as [UD03_Assy_Number02],
	[UD03_Assy].[Number03] as [UD03_Assy_Number03],
	(sum(AssyMatl.Calculated_AssyMatlSum)) as [Calculated_AssyMatlSum],
	[AssyMatl].[Calculated_AssyMatlBurSum] as [Calculated_AssyMatlBurSum],
	[AssyLabor].[Calculated_AssyLabor] as [Calculated_AssyLabor],
	[AssyLabor].[Calculated_AssyBurden] as [Calculated_AssyBurden],
	(isnull(sum(isnull(AssySubContract.Calculated_AssySubSum,0))+
 sum(isnull(AssyMisc.Calculated_AssyMiscSum,0)),0)) as [Calculated_AssySubMisc]
from Ice.UD03 as UD03_Assy
left outer join  AssyMatl  as AssyMatl on 
	UD03_Assy.Company = AssyMatl.UD03_AssyMatl_Company
	and UD03_Assy.Key1 = AssyMatl.UD03_AssyMatl_Key1
	and UD03_Assy.Key2 = AssyMatl.UD03_AssyMatl_Key2
left outer join  AssyLabor  as AssyLabor on 
	UD03_Assy.Company = AssyLabor.UD03_Assy_Labor_Company
	and UD03_Assy.Key1 = AssyLabor.UD03_Assy_Labor_Key1
	and UD03_Assy.Key2 = AssyLabor.UD03_Assy_Labor_Key2
left outer join  AssySubContract  as AssySubContract on 
	UD03_Assy.Company = AssySubContract.UD03_AssySub_Company
	and UD03_Assy.Key1 = AssySubContract.UD03_AssySub_Key1
	and UD03_Assy.Key2 = AssySubContract.UD03_AssySub_Key2
left outer join  AssyMisc  as AssyMisc on 
	UD03_Assy.Company = AssyMisc.UD03_AssyMisc_Company
	and UD03_Assy.Key1 = AssyMisc.UD03_AssyMisc_Key1
	and UD03_Assy.Key2 = AssyMisc.UD03_AssyMisc_Key2
where (UD03_Assy.ShortChar01 = 'Header'  and UD03_Assy.Key2 <> '000')
group by [UD03_Assy].[Company],
	[UD03_Assy].[Key1],
	[UD03_Assy].[Key2],
	[UD03_Assy].[Number02],
	[UD03_Assy].[Number03],
	[AssyMatl].[Calculated_AssyMatlBurSum],
	[AssyLabor].[Calculated_AssyLabor],
	[AssyLabor].[Calculated_AssyBurden]
union all
select 
	[Assembly].[UD03_Assy_Company] as [UD03_Assy_Company],
	[Assembly].[UD03_Assy_Key1] as [UD03_Assy_Key1],
	[Assembly].[UD03_Assy_Key2] as [UD03_Assy_Key2],
	[Assembly].[UD03_Assy_Number02] as [UD03_Assy_Number02],
	[Assembly].[UD03_Assy_Number03] as [UD03_Assy_Number03],
	[Assembly].[Calculated_AssyMatlSum] as [Calculated_AssyMatlSum],
	[Assembly].[Calculated_AssyMatlBurSum] as [Calculated_AssyMatlBurSum],
	[Assembly].[Calculated_AssyLabor] as [Calculated_AssyLabor],
	[Assembly].[Calculated_AssyBurden] as [Calculated_AssyBurden],
	[Assembly].[Calculated_AssySubMisc] as [Calculated_AssySubMisc]
from  Assembly  as Assembly
inner join  TopLevel  as TopLevel1 on 
	Assembly.UD03_Assy_Key1 = TopLevel1.TopLevelAssy_Key1
	and Assembly.UD03_Assy_Number03 Like cast(TopLevelAssy_Key2 as decimal))

select 
	[UD03].[ShortChar02] as [UD03_ShortChar02],
	[UD03].[Key1] as [UD03_Key1],
	[UD03].[Key2] as [UD03_Key2],
	[UD03].[Number03] as [UD03_Number03],
	[UD03].[Number02] as [UD03_Number02],
	((case when UD03.ShortChar03='99990' then 'Stock' else 'Order' end)) as [Calculated_MakeToType],
	(1) as [Calculated_JobComplete],
	[TopLevel].[Calculated_MaterialSum] as [Calculated_MaterialSum],
	[TopLevel].[Calculated_MtlBurSum] as [Calculated_MtlBurSum],
	[TopLevel].[Calculated_LaborCost] as [Calculated_LaborCost],
	[TopLevel].[Calculated_BurdenCost] as [Calculated_BurdenCost],
	[TopLevel].[Calculated_TopLevelSubMisc] as [Calculated_TopLevelSubMisc],
	(isnull(sum(Assembly1.Calculated_AssyMatlSum),0)) as [Calculated_LLAMaterial],
	(isnull(sum(Assembly1.Calculated_AssyMatlBurSum),0)) as [Calculated_LLAMatlBurden],
	(isnull(sum(Assembly1.Calculated_AssyLabor),0)) as [Calculated_LLALabor],
	(isnull(sum(Assembly1.Calculated_AssyBurden),0)) as [Calculated_LLABurden],
	(isnull(sum(Assembly1.Calculated_AssySubMisc),0)) as [Calculated_LLASubMisc]
from Ice.UD03 as UD03
left outer join  TopLevel  as TopLevel on 
	UD03.Key1 = TopLevel.TopLevelAssy_Key1
	and UD03.Key2 = TopLevel.TopLevelAssy_Key2
left outer join  Assembly  as Assembly1 on 
	UD03.Key1 = Assembly1.UD03_Assy_Key1
where (UD03.ShortChar01 = 'Header'  and UD03.Key1 = '2-7881-01')
group by [UD03].[ShortChar02],
	[UD03].[Key1],
	[UD03].[Key2],
	[UD03].[Number03],
	[UD03].[Number02],
	((case when UD03.ShortChar03='99990' then 'Stock' else 'Order' end)),
	[TopLevel].[Calculated_MaterialSum],
	[TopLevel].[Calculated_MtlBurSum],
	[TopLevel].[Calculated_LaborCost],
	[TopLevel].[Calculated_BurdenCost],
	[TopLevel].[Calculated_TopLevelSubMisc]
order by UD03.Key1, UD03.Number03, UD03.Key2
16

Here are my tables…

I’m using the individual CTE’s to sum different record types.

TopLevel Material
TopLevel Labor
TopLevel Subcontract
TopLevel Misc
TopLevel (combines all of the above)

Assembly Material
Assembly Labor
Assembly Subc
Assembly Misc
Assembly (combines all of those)

AssemblyUNION

VAXQuery (Top Level)

image

17

Okay, it looks like the total “Lower Level” Assembly Costs are the sums of anything that isn’t Assy 0.

So… its just totaling ALL subassemblies on the specific JobNum.

18

Yes, that sounds correct based on the SQL. I believe what is missing in a central table that has the following structure.

JobNum Asm Child ASM
12345 0 1
12345 1 2
12345 1 3

Is the data structured in the UD table like this at all?

19

I have a Parent ASM column (Number03), not a child.

20

So, you have.

JobNum Asm ParentAsm
12345 1 0
12345 2 1
12345 3 1