I have a typical recursive BAQ that gets all of the child materials in a parent part, multi-level, etc.
That works well, see below for the subquery layout.
I have a decent understanding of how this works, so far.
The challenge is-I also need is the parent part to show up just once in the results as a single
row so that I can properly do some ‘stuff’ downstream. I have tried a bunch of stuff as far as a second union between the first union and the top level, I understand how the display columns must match, etc. I believe I am just missing a concept here , maybe it can’t be done in just 1 BAQ. Example parent part: 566-1R , we have 10 total material parts when we dig thru all levels.
In the example shown I'd like that parent part row to be present.as highlighted. I know how to setup another UnionAll but I think I am not placing it correctly. Part numbers masked here.mostly..
That said, I don’t quite get how you want your logic set up, because I don’t know what you’re query is doing, nor do I know where you’re stuck.
If you have a single anchor this is pretty easy, because you’ll have one “top level row” that you can simply union. If you have multiple top levels in the same results set, it becomes trickier, but still probably doable through parameters.
Id like to see more of your Query… Also if you build a Recursive CTE, you should have access to Calculated_Level == 0 You would usually start at PartRev or something and work your way down to PartMtl or starting at JobProd
This one is a bit unorganized (old proto)… But I know when I needed a UnionAll with Inner-SubQueries. I had to make use of the parenthesis basically to assist in deciding what goes together, what doesn’t.
Thanks Brandon, I was only allowed one ‘graphic’ as I just re-joined. Subquery set up, fairly typical I think, at least for a ‘bill of material’ recursive: I think I need to get another ‘union’ that joins Part table to the sub 1 CTE, something like this: but I’m not sure where to put it in the Subs List? I’ve tried after the first Union, before top level, does not work there. My apologies, I realize I’m explaining this very well. ONly 1 graphic allowed so this is the other ‘union all’ I want to get into my subs list
The usual way to do that is at the first step before recursion begins: select parts which have no parent part as your ‘MtlPart Number’ and deliver null as the parent part. You can select that as the top of your hierarchy as ‘EndItem’ too. Then recurse on part = prior material to drill down the hierarchy.
If what you actually need is a list of parts where any material part exists whether or not they’re at the top of the hierarchy and you’re working with the results outside of BAQ, yeah, I’d make that a second dataset just to reduce data transfer volume. Including descriptions and so on too so the hierarchy only returns hierarchy info.
If it all has to pile up and get consumed in a single query then it’ll be more straightforward to union-all / staple those rows onto your ‘top level’ query instead of trying to wedge them into recursion somehow.
Are you able to export your BAQ and share it. I think there is a better way, that will also run much faster. Easier to see all your where conditions etc.
If your goal is merely to populate the first two columns of the BAQ with parent part into, I’m not sure why you’re not grabbing those fields as the anchor of the CTE. The recursion might be looping through PartMtl records, but that doesn’t mean the anchor has to as well. As you know, the columns just have to match up.
