# Epicor BAQ Subquery Types Reference: TopLevel, InnerSubQuery, Union, UnionAll and CTE > Source: https://netray.co/tools/epicor-baq-subquery-types-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer What each BAQ Designer SubQuery Type generates in SQL, the ordering rules that make unions and recursive CTEs work, and tested pattern recipes for BOMs, demand lists and last operations. ## Category epicor ## Erp Epicor Kinetic ## Audience - Epicor developer - BAQ author - ERP administrator - Report writer ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### SubQuery Type or recipe **Key:** type **Type:** text #### SQL it produces **Key:** sqlEquivalent **Type:** text #### Rule of use **Key:** ruleOfUse **Type:** text #### Typical use **Key:** typicalUse **Type:** text #### Common error **Key:** commonError **Type:** text **Rows:** **Type:** TopLevel **Sql Equivalent:** The outer SELECT whose columns are the BAQ result set **Rule Of Use:** Exactly one TopLevel per BAQ. Its Display Fields become the BAQ columns used by dashboards, BAQ reports and the BaqSvc REST endpoint. **Typical Use:** Final output of every BAQ; joins extra tables (Part, Customer) to the rows built by CTEs and unions **Common Error:** "No top level subquery" when every subquery has been set to another type **Type:** InnerSubQuery **Sql Equivalent:** A derived table (SELECT ...) joined in the FROM clause, or a subquery used in criteria **Rule Of Use:** Drag it onto the Phrase Build canvas of another subquery like a table and join on its display fields. Only its display fields are visible to the outer query. **Typical Use:** Aggregates (MAX, SUM, COUNT) grouped per key, then joined back to detail rows **Common Error:** Joining on a field that is not in the inner subquery display list, or forgetting Group By on non-aggregated columns **Type:** Union **Sql Equivalent:** UNION (removes duplicate rows) **Rule Of Use:** Must return the same number of columns, in the same order, with compatible data types as the subquery it unions with. Place it directly after that subquery in the subquery list. **Typical Use:** Merging two sources into one list where exact duplicate rows should collapse **Common Error:** Column count or data type mismatch between the members; slower than UnionAll because of the duplicate check **Type:** UnionAll **Sql Equivalent:** UNION ALL (keeps every row) **Rule Of Use:** Same column rules as Union. In a recursive build it is the recursive member that joins back to the CTE. **Typical Use:** Combining open jobs and open PO releases into one supply list; recursive member of an indented BOM **Common Error:** Column mismatch; placing it after the TopLevel instead of after the subquery it extends **Type:** CTE **Sql Equivalent:** WITH name AS (...) common table expression; the anchor of a recursive CTE **Rule Of Use:** Can be referenced by later subqueries as if it were a table. For recursion the order is CTE, then UnionAll that references the CTE, then TopLevel that reads the CTE. **Typical Use:** Indented BOM, where-used chains, reusable filtered sets **Common Error:** Trying to save or test before a TopLevel exists; wrong order in the subquery list **Type:** Except **Sql Equivalent:** EXCEPT (rows in the first set that are not in the second) **Rule Of Use:** Same column count and type rules as Union. Use list parentheses to control which subqueries are grouped before the Except is applied. **Typical Use:** Parts with demand but no open supply; customers in one list but not another **Common Error:** Grouping applied to the wrong members because parentheses were not set in the subquery list **Type:** Recipe: recursive BOM **Sql Equivalent:** WITH BOM AS (anchor UNION ALL recursive) SELECT ... FROM BOM **Rule Of Use:** Subquery 1 CTE on PartMtl for the top part. Subquery 2 UnionAll: PartMtl joined to the CTE on PartMtl.PartNum = CTE.PartMtl_MtlPartNum. Subquery 3 TopLevel reads the CTE. **Typical Use:** Multi-level indented BOM from Erp.PartMtl **Common Error:** Rows multiply when a child part has several approved revisions **Type:** Recipe: level counter **Sql Equivalent:** 1 in the anchor, Level + 1 in the recursive member **Rule Of Use:** Calculated field Level (int) = 1 in the CTE; in the UnionAll the calculated field is CTE.Calculated_Level + 1. Same name and type in both. **Typical Use:** Indent depth for BOM display, stop condition Level < 10 **Common Error:** Different data types in anchor and recursive member, which SQL Server rejects **Type:** Recipe: sort path **Sql Equivalent:** CAST(... AS nvarchar(...)) concatenation of padded MtlSeq values **Rule Of Use:** Anchor: padded MtlSeq. Recursive member: CTE path + '.' + padded MtlSeq, cast to the same length as the anchor. **Typical Use:** Order the TopLevel rows so children sit under their parent **Common Error:** Length mismatch between anchor and recursive cast **Type:** Recipe: union open jobs and PO releases **Sql Equivalent:** SELECT from JobHead UNION ALL SELECT from PORel **Rule Of Use:** Subquery 1 (TopLevel) on JobHead with JobClosed = false; Subquery 2 (UnionAll) on PORel with OpenRelease = true. Give both the same calculated columns: Source, PartNum, DueDate, Qty. **Typical Use:** One supply list per part for planners **Common Error:** Text column in one member and date in the other at the same position **Type:** Recipe: last operation per job **Sql Equivalent:** Derived table with MAX(OprSeq) GROUP BY JobNum **Rule Of Use:** InnerSubQuery on JobOper: Company, JobNum (group by), calculated MaxOpr = MAX(JobOper.OprSeq). Join it to JobOper on Company, JobNum and OprSeq = MaxOpr in the TopLevel. **Typical Use:** Final operation, its resource group and completion status **Common Error:** Grouping by AssemblySeq too and getting one row per assembly instead of per job **Type:** Recipe: subquery criteria vs table criteria **Sql Equivalent:** WHERE on the subquery result vs WHERE on one table **Rule Of Use:** Table criteria filter rows of that table before joins. Subquery criteria (SubQuery Criteria tab) compare against fields or other subqueries, for example NOT EXISTS style filters. **Typical Use:** Parts with no PartTran in the last 12 months **Common Error:** Putting a filter on the outer side of a left join as table criteria and turning it into an inner join **Type:** Recipe: union with a constant source column **Sql Equivalent:** SELECT 'JOB' AS Source ... UNION ALL SELECT 'PO' AS Source ... **Rule Of Use:** Add a character calculated field with the same length in each member so rows stay identifiable after the union. **Typical Use:** Demand and supply lists, exception reports **Common Error:** Different nvarchar lengths causing truncation or type errors **Type:** Recipe: where-used chain **Sql Equivalent:** Recursive CTE walking from component to parent **Rule Of Use:** CTE on PartMtl where MtlPartNum = the component; UnionAll joins PartMtl.MtlPartNum = CTE.PartMtl_PartNum; TopLevel reads the CTE. **Typical Use:** Impact of a component change or supplier issue **Common Error:** Endless loop when a BOM references itself; keep a level limit **Type:** Recipe: Except for parts without supply **Sql Equivalent:** SELECT PartNum FROM demand EXCEPT SELECT PartNum FROM supply **Rule Of Use:** Make the demand side a CTE or first member, the supply side the Except member, both returning only PartNum (and Company). **Typical Use:** Shortage pre-check before MRP **Common Error:** Extra columns in one member, so nothing is ever removed **Type:** Recipe: InnerSubQuery totals joined back **Sql Equivalent:** LEFT JOIN (SELECT key, SUM(x) GROUP BY key) **Rule Of Use:** Aggregate in the inner subquery, join with a left join in the TopLevel so rows without totals stay. **Typical Use:** Open order value per customer next to customer master columns **Common Error:** Aggregating in the TopLevel and multiplying totals because of a one to many join **Type:** Recipe: parentheses in the subquery list **Sql Equivalent:** (A UNION ALL B) EXCEPT C **Rule Of Use:** Set the grouping parentheses on the subquery list so set operators apply in the order you intend. **Typical Use:** Mixed Union, UnionAll and Except in one BAQ **Common Error:** Set operator applied to the wrong pair, giving too many or too few rows **Notes:** Set the type in BAQ Designer: select the subquery, open the SubQuery Options tab, choose SubQuery Type. The Kinetic web BAQ Designer offers the same type list as the ERP 10.2 client. CTE field references in later subqueries use the CTE alias and the display field name (for example PartMtl_MtlPartNum). Check the exact label text in your release; Intersect is not listed here because we could not confirm it for every version. ## Content **Intro:** This reference explains every SubQuery Type in the Epicor ERP 10 and Kinetic BAQ Designer, the SQL each one generates and the ordering rules that decide whether a union or recursive CTE runs. The recipe rows give the exact subquery layout for indented BOMs, combined supply lists and last-operation queries. **Sections:** #### How to use it **Body:** Search the table for the type or the job you are trying to do, for example 'recursive' or 'union'. The first six rows are the types themselves; the recipe rows show how they combine. Before building a union, list the columns each member returns and make the count, order and data types identical, including calculated fields. For recursion, build the CTE first, test it, then add the UnionAll and the TopLevel. Export the sheet to Excel and add your own house patterns as extra rows. **Bullets:** - One TopLevel per BAQ, always - Union members: same column count, order and compatible types - Recursive: CTE, then UnionAll referencing the CTE, then TopLevel - Use parentheses in the subquery list for mixed set operators #### Where this lives in Epicor **Body:** Open Executive Analysis > Business Activity Management > General Operations > Business Activity Query (BAQ Designer) in the 10.2 client, or the BAQ Designer application in the Kinetic web client. Each BAQ has a subquery list on the Query Builder; select a subquery and use SubQuery Options > SubQuery Type to change it. The Phrase Build canvas is where you drag tables, CTEs and inner subqueries and set joins. The General tab shows the generated query text, which is the quickest way to confirm that your types produced the UNION, EXCEPT or WITH clause you expected. #### Worked example: 3-level BOM with a CTE **Body:** Subquery 1 (CTE): table PartMtl, criteria PartNum = 'FG-1000' and RevisionNum = 'A', display PartNum, RevisionNum, MtlSeq, MtlPartNum, QtyPer and calculated Level = 1. Subquery 2 (UnionAll): PartMtl joined to the CTE on Company and PartMtl.PartNum = CTE.PartMtl_MtlPartNum, a join to PartRev with Approved = true to pick the child revision, the same display columns in the same order, Level = CTE.Calculated_Level + 1 and criteria Calculated_Level < 3 on the CTE. Subquery 3 (TopLevel): reads the CTE and joins Part for the description. Test after each step. #### Version differences and gotchas **Body:** The BAQ Designer will not save or test a CTE until a TopLevel exists; a common workaround is to build each piece as TopLevel first and change the types later. Rows multiply when a child part has more than one approved revision, so restrict to one revision per part. Recursive members must return the same data types as the anchor: cast calculated fields explicitly. The Designer does not accept pasted SQL; translate SQL into subqueries. Large recursive BAQs can time out on the BaqSvc endpoint, so test with one top part first. **Cta:** If you want planners to ask Epicor for indented BOMs or shortage lists in plain English on your own servers, we can set that up over your existing BAQs. **Faq:** #### What are the SubQuery Types in an Epicor BAQ? **Answer:** The common types are TopLevel (the outer query that returns rows), InnerSubQuery (a derived table or criteria subquery), Union, UnionAll, CTE (a WITH clause, also the anchor of recursion) and Except. Set the type on the SubQuery Options tab. Every BAQ needs exactly one TopLevel; the others feed it. #### How do I make a union in an Epicor BAQ? **Answer:** Build the first subquery as TopLevel, add a second subquery and set its type to Union or UnionAll. Both must return the same number of columns in the same order with compatible types, so use calculated fields to fill gaps. Place the union member directly after the subquery it unions with. UnionAll is faster when duplicates do not matter. #### How do I recursively join PartMtl for a multi-level BOM? **Answer:** Use three subqueries in this order: a CTE on PartMtl for the top part, a UnionAll that joins PartMtl.PartNum to the CTE's MtlPartNum display field, and a TopLevel that selects from the CTE. Add a Level calculated field (1, then Level + 1) and a level limit so a looping BOM cannot run forever. #### Why does my BAQ say there is no top level subquery? **Answer:** Every subquery has a type other than TopLevel, often after converting the main query to a CTE. Add or change one subquery to TopLevel on the SubQuery Options tab. Only one is allowed, and it must come after the CTE and union members it reads in the subquery list. ## Search terms epicor baq subquery types, epicor baq subquery, epicor subquery baq, epicor baq union example, epicor baq union, epicor baq how to recursive join mtlparts, epicor baq cte, epicor baq no top level subquery --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json