EpicorFree referencePDF, Excel, Word exportEpicor KineticAll Epicor ERP (Kinetic, 10, 9, Vantage) tools

Epicor BAQ Subquery Types Reference: TopLevel, InnerSubQuery, Union, UnionAll and CTE

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.

Download:

SubQuery Type or recipe	SQL it produces	Rule of use	Typical use	Common error
TopLevel	The outer SELECT whose columns are the BAQ result set	Exactly one TopLevel per BAQ. Its Display Fields become the BAQ columns used by dashboards, BAQ reports and the BaqSvc REST endpoint.	Final output of every BAQ; joins extra tables (Part, Customer) to the rows built by CTEs and unions	"No top level subquery" when every subquery has been set to another type
InnerSubQuery	A derived table (SELECT ...) joined in the FROM clause, or a subquery used in criteria	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.	Aggregates (MAX, SUM, COUNT) grouped per key, then joined back to detail rows	Joining on a field that is not in the inner subquery display list, or forgetting Group By on non-aggregated columns
Union	UNION (removes duplicate rows)	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.	Merging two sources into one list where exact duplicate rows should collapse	Column count or data type mismatch between the members; slower than UnionAll because of the duplicate check
UnionAll	UNION ALL (keeps every row)	Same column rules as Union. In a recursive build it is the recursive member that joins back to the CTE.	Combining open jobs and open PO releases into one supply list; recursive member of an indented BOM	Column mismatch; placing it after the TopLevel instead of after the subquery it extends
CTE	WITH name AS (...) common table expression; the anchor of a recursive CTE	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.	Indented BOM, where-used chains, reusable filtered sets	Trying to save or test before a TopLevel exists; wrong order in the subquery list
Except	EXCEPT (rows in the first set that are not in the second)	Same column count and type rules as Union. Use list parentheses to control which subqueries are grouped before the Except is applied.	Parts with demand but no open supply; customers in one list but not another	Grouping applied to the wrong members because parentheses were not set in the subquery list
Recipe: recursive BOM	WITH BOM AS (anchor UNION ALL recursive) SELECT ... FROM BOM	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.	Multi-level indented BOM from Erp.PartMtl	Rows multiply when a child part has several approved revisions
Recipe: level counter	1 in the anchor, Level + 1 in the recursive member	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.	Indent depth for BOM display, stop condition Level < 10	Different data types in anchor and recursive member, which SQL Server rejects
Recipe: sort path	CAST(... AS nvarchar(...)) concatenation of padded MtlSeq values	Anchor: padded MtlSeq. Recursive member: CTE path + '.' + padded MtlSeq, cast to the same length as the anchor.	Order the TopLevel rows so children sit under their parent	Length mismatch between anchor and recursive cast
Recipe: union open jobs and PO releases	SELECT from JobHead UNION ALL SELECT from PORel	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.	One supply list per part for planners	Text column in one member and date in the other at the same position
Recipe: last operation per job	Derived table with MAX(OprSeq) GROUP BY JobNum	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.	Final operation, its resource group and completion status	Grouping by AssemblySeq too and getting one row per assembly instead of per job
Recipe: subquery criteria vs table criteria	WHERE on the subquery result vs WHERE on one table	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.	Parts with no PartTran in the last 12 months	Putting a filter on the outer side of a left join as table criteria and turning it into an inner join
Recipe: union with a constant source column	SELECT 'JOB' AS Source ... UNION ALL SELECT 'PO' AS Source ...	Add a character calculated field with the same length in each member so rows stay identifiable after the union.	Demand and supply lists, exception reports	Different nvarchar lengths causing truncation or type errors
Recipe: where-used chain	Recursive CTE walking from component to parent	CTE on PartMtl where MtlPartNum = the component; UnionAll joins PartMtl.MtlPartNum = CTE.PartMtl_PartNum; TopLevel reads the CTE.	Impact of a component change or supplier issue	Endless loop when a BOM references itself; keep a level limit
Recipe: Except for parts without supply	SELECT PartNum FROM demand EXCEPT SELECT PartNum FROM supply	Make the demand side a CTE or first member, the supply side the Except member, both returning only PartNum (and Company).	Shortage pre-check before MRP	Extra columns in one member, so nothing is ever removed
Recipe: InnerSubQuery totals joined back	LEFT JOIN (SELECT key, SUM(x) GROUP BY key)	Aggregate in the inner subquery, join with a left join in the TopLevel so rows without totals stay.	Open order value per customer next to customer master columns	Aggregating in the TopLevel and multiplying totals because of a one to many join
Recipe: parentheses in the subquery list	(A UNION ALL B) EXCEPT C	Set the grouping parentheses on the subquery list so set operators apply in the order you intend.	Mixed Union, UnionAll and Except in one BAQ	Set operator applied to the wrong pair, giving too many or too few rows

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.

Stuck on a BAQ that will not union or recurse?

Send us the BAQ export and the result you need, and we will tell you which subquery types and order to use.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

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.

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

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

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

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.

Frequently Asked Questions

What are the SubQuery Types in an Epicor BAQ?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools