Union 2 BAQ sub-queries, and join the result of that union to another table

Epicor ERP 10
1

Any way to make a union between 2 sub queries, and then join that result another table?

I want a union of Part and PartSub, where Part.PartNum = UserParam and PartSub.SubPart = UserParam.

Table: Part
PartNum    
RM-0001
RM-0002
RM-0003
XYZPQD
XYZXXX


Table: PartSubs
PartNum  SubPart
XYZPQD   RM-0001
XYZXXX   RM-0003
XYZYYY   RM-0003

Desired Results

UserParam = RM-0001
(2) Records
PartNum
RM-0001
XYZPQD


UserParam = RM-0002
(1) Record
PartNum
RM-0002


UserParam = RM-0003
(3) Records
PartNum
RM-0003
XYZXXX
XYZYYY

Then join those results to another table (PartTran)

2

I’ll try to conceptually help if I can.
If you create the first query as a sub-query and the second query as a subquery, and assuming the two sub queries match columns, you can create a union query from those. On that union, you can create a subquery from it and then join the new unionized (hah) to whatever you want.

3

First off … I hate that the top level query is still referred to as a “sub-query”.

So I’d have the following three sub-queries?

  • SubQuery1 of type ‘TopLevel’
  • SubQuery2 of type ‘InnerSubQuery’
  • SubQuery3 of type ‘Union’

With SubQuery2 and SubQuery3 having the same columns (as required by unions)

And then only include and join SubQuery2 in the layout of SubQuery1?

How does it know to limit the union between the SQ’s 2 & 3?

4

If the union is then encapsulated inside another sub-query, it will behave as any other query would.
I think you need more subs than 3, since you need to wrap the union up too.

5

I got it working with 3 subqueries (counting the TopLevel one)

I needed to add parenthesis on the SubQueryList tab
image

I now get the desired output. I enter CB-0001 (CB-0001 is an Alternate for the now inactive XCHM#8)

image
image.png694×377 26.1 KB

6

Glad to hear it, good work!

7

(((Hmmm… Why have I never seen those parentheses on this page before?)))
Thanks for the tip!

8

I always rename my subs so I know what they heck they are. My top level I rename to “TopLevel”

9

I name mine ‘Main’

I was just experimenting in the example above. I’ll name them properly when I make the full BAQ

10

I just spent so much time trying to get an AI tool to help me write a union in the designer that does this, I finally gave up and found this thread. You’re the man.