BAQ Union/Union All - Help Needed

ERP 10
#1

I am building a baq that looks at the part mtl table and pulls out the parent part and mtl part. I then build 5 sub-queries linking mtl part to part num of the next query down. This gives me a blow out of a bom for each parent part.

What I want to do next is link all the subqueries together using a Union or Union all statement like the one below:

image
image1024×158 4.77 KB

The idea is to have all mtl parts listed next to their primary parent part. However, as I click to analyze the Epicor Crashes. Has anyone else done something similar or have an idea on how to get this working?

#2

You need a CTE if you want them linked. A union will just layer them on top of each other, but other than needed the same fields, aren’t related.

1 Like
#3

If you really want to get fancy, use a recursive CTE and have it stop at 5 recursions.

1 Like
#4

@Banderson,
How does a CTE work exactly? Ive not used them and would like a quick rundown on what makes them different from an InnerSubQuery. Additionally in this instant would I change all my InnersubQueries to CTE’s?

@Doug.C,
Do you have an example of how this would work? Currently, I have it as a primary InnerSubQuery and I just reuse it and a new PartMtl table to go to the next layer down. Is this close to what you mean?

#5

Look in the tools guide, there is a step example of exactly what you are trying to do. Basically a CTE lets you reference the same query and recursively loop through your data, then it adds all of the rows into a union.

image
image932×906 106 KB

1 Like
#6

You can make a recursive BOM indenter as mentioned, however, with your current set up the only thing you need to do to make it union is to add several UnionAlls which have the subqueries inside of them. As shown below. I use this method because RecursiveCTE indenters don’t like group by functions in the anchor, and I don’t want duplicates of a part within a level.

The union all basically rolls up whatever fields from what is inside it’s subquery into the subquery. In my instance I roll up all the levels, into Level5 subquery, and then put that subquery into my mainquery. Looks ugly, but it works nice for my application

image
image876×664 29.5 KB

1 Like
#7

Here is an example. RecursionExample.baq (55.4 KB)

image
image1000×186 30.6 KB

SubQuery Description
MaxRev I use this as a filter on the Recursion subquery. It returns the max approved rev
MainAssembly This is the Top or Level 0 part of the BOM.
Recursion* This returns all parts associated down the BOM for the current part level
FINAL This is where I place the MainAssembly CTE.

*Note: On the Recursion, I have put a SubQuery Criteria of the Calculated Level Field <= 5. This limits the BOM to 5 levels down.

The output is like the following:

image
image822×313 18.7 KB

Which can be looked at like:

  • MainPart
    • SubComponent1
      • SubComponent1.1
        • Subcomponent1.1.1
    • SubComponent2
      • SubComponent2.1
        • Subcomponent2.1.1
    • SubComponent3
      • SubComponent3.1
    • SubComponent4
3 Likes
#8

@Doug.C,
This is awesome! It seems to be acting a little differently than I would like though. I’m trying to get it to where it would show like below:

image

this way the primary main part is always in the second column. What would I need to change that?

#9

Is this what you were looking for?

RecursionExample2.baq (55.9 KB)

1 Like
#10

@jdewitt6029 this is an example of CTE for BOMS.

#11

@Doug.C,

My appologies! The original post had exactly what I was after. I had accidentally changed a join field from what you originally had. Credit for the solution to you. Thanks to all who participated! Thumbs up all around!