So I am VERY inexperienced at creating complex BAQs, and I need a report that explodes our indented BOMs. I am trying to follow the steps in this training: Sign In
I cannot get past the first step to create the CTE because Epicor says my query contains no top level subquery. Are there steps in front of this training that I am missing? Is there anyplace this process is spelled out completely from start to finish? Thank you!
I’m guessing you just tried to test the BAQ before you had completed all the steps in the instructions? If so, this is a common error… due to a chicken/egg situation while working with CTE/UNION.
Try completing every step in the instructions before you analyze/test your BAQ.
Build BOM in Query - ERP 10 - Epicor User Help Forum (epiusers.help)
quite a few topics on this. try and search for BOM if this doesn’t work
1 Like
Haven’t gotten anywhere near testing anything yet. I am trying to follow the steps exactly as they are laid out in the training link I shared. The very first step creates a CTE query, but I cannot save it. That’s where I am getting the message that I need a top level subquery.
I know there are lots of topics on creating BOMs with pieces of information, but I cannot understand most of what I am reading. Hoping to locate complete step-by-step instructions.
Thanks!
Similar to comments on testing… where you (usually) need to finish all steps before you’ll be able to save… or test.
Attached a very simple example if you want to try importing… note this example doesn’t account for revisions on lower level materials. That problem will be discussed separately in several threads here.
CUS-IndentedBOM.baq (45.7 KB)
1 Like
I followed the instructions exactly as indicated in the training, and that first CTE BAQ is very simple. Just eight steps. I just cannot save it. Not sure how I can be doing anything wrong?
How do I use the sample you provided? How do I open the file to get it into Epicor?
Thanks!
to resolve your error.
The BAQ is looking for one subquery to be type toplevel.
You can change the type on the SubQuery Options Tab.
The links to the other discussions was to help you with your overall BOM creation.
Posting a picture of where you are, helps us help you. 
The training does not say anything about creating a top level sub-query. Guessing if I change the first subquery (CTE) to top level, I will not get the desired results?
Should be able to import to BAQ Designer
To do a CTE query, you need three subqueries
- CTE
- UNIONALL
- TOP
They need to be in the above order to get the data you want.
- CTE is the “seed” query that gets the first set of starting data
- UNIONALL gets the balance of the data and joins to the CTE
- TOP shows the results of the CTE. In this top, you can also join to other tables to get additional data that you may need.
So I stopped trying to save at the end of each step as indicated in the instructions, and I worked all the way thru the training. One of the last steps created a top level query, and then I was able to save all my work. Guess advising to save each step is just an error in the training instructions. Thanks much for all your responses!
Yup… you are correct. Once you have a CTE, you cannot save. BUT… you can do this:
- create a TOP query called “cte1” that will eventually be your CTE. See the results. Change to a Inner-Subquery
- Create a new Top called “Union1” that will eventually be your unionall query. Build out the next layer. Make sure that all your fields are the same types (as required by Union all). Test to see results.
- NOW change the first query to CTE, and the second query to UNIONALL.
- Create your Top Query.
If you do it this way, you can save along the way (until step 3).
Thanks so much, Tim. You rock!!!
1 Like
The indented BAQ BOOM works perfectly for me,
I get a question:
Is there a way to use the BAQ to query a parts list at the same time and display the result on a board?
Any idea how to do it?