I am using Kinetic. I have used Union All in quite a few BAQ, but only between two tables.
I need to union 5 subqueries together. When I try the same method I don’t get the expected results. I have read other articles on here, but they either talk about it being recursive, or that you would have 4 of the five set to UNION and the final one being your TOP LEVEL and Epicor does the rest.
But I need this to be within the query to create a master list of every Company/Plant/Part/Rev combination we hold
I will then use that master list to join to my top-level query
Take a look at how I currently have it set, I’ve tried using brackets. When I have done this style with 2 subquery in the past it has worked, you have a CTE, then the next subquery down as a UNION ALL. Then you make an INNER SUBQUERY after it and bring the CTE through to it and it works. they join together.
But with five it’s no dice.
I’ve tried using AI and it really doesn’t get how Epicor handles union. And I’ve looked on other articles here and they are all on the client version of BAQ designer which again is no help.
Interested to hear your thoughts please.
Each subquery contains the fields Company, Plant, Part, Revision
My aim is to get one master list of all the Company/Plant/Part/Revisions held in the 5 queries.
It’s not because there are five, it’s something else. It’s the same rules for five as for two. Each query appended by UNION or UNION ALL needs to select a set of fields that are canonically and type identical. Think of each UNION ALL pair as its own independent pair and how that has worked successfully, then do that five times. In reality that’s even how they’ll be appended, two sets at a time.
It’s not necessary to bother with a common table expression unless there’s a need for recursion. Unless it’s better for you that way, then that’s a totally legit reason. Sometimes a CTE improves brevity and readability in the text output, for example if a ‘subquery’ gets referenced in multiple places.
edit - Here’s an example (maybe extreme?) of one I have monitoring all the ways that part configurations can go problematically askew. The common table expressions each return a detail that gets used all over the place. Could pop each into a subquery all the way at the end but BAQ would spam copies of it as text everywhere it’s referenced, which is awful to read and I don’t trust BAQ to not confuse itself in the process.
Each step in the series returns nvarchar, nvarchar, nvarchar, boolean and as long as they all do it’s all good.
Gonna pop this one behind a spoiler with a CW: scary SQL stuff,possiblydefinitely cursed.
