I want to create a BAQ which will result in 1 table.
These are the fields that I have in my 2 subqueries (1 subquery per table):
I want the result to be an union of these 2 tables so ECOMtl_PartNum should be in the same column as ECOOpr_PartNum.
So far, I have this but I don’t know at all how to do it in BAQ (would be easy to do in SQL):
I know that but how can I say that PartNum from both tables should be in the same column? Maybe my design (1 main query with 2 subqueries) is not right?
When setting the Subquery Type to Union, the fields selected must be the same type and order as the Top Level Query. It appears you have it set up correctly. What results are you getting that do not seem right?
Should I have 1 Top Level Query with 2 Subqueries or should I get 1 Top Level (Material) with 1 subquery (Operation) with type = Union?
Because so far, in my Top Level Query, i can decide either the Material.PartNum or Operation.PartNum which is not right because it should be the same column to show either Material or Operation rows.
Use the Material query as your Top Level, Union with the Operation query.
Oh well, that was the problem I guess. It seems to be working now. Thanks will look further into this to validate my data then
This has been resolved for about 18 months, but I do have a question along the same lines:
I have been charged with creating a report that combines open AP Data with open AP Invoices. Not every AP invoice is created from a PO so I dont see a way to create this from the invoices. There can be open PO that we have not recevied an invoice on either so a join that way does not work. The best option is to create a top query with all the fields (place holders where needed) and then a union ALL (place holders where needed). If i do this can I group on the top level to get one record where there is a match from PO to invoice and show all Open PO and Open invoices where need?
In straight SQL I would create the union - Something like this (this is jsut a concept as each query would have about 20 fields)
SELECT
PO!Po_Number as PO_Number,
PO!Amount as PO_Amount,
Null as Inv_Number,
0 as Inv_Amount;
UNION
SELECT
Null as PO_Number,
0 as PO_Amount,
InvcHdr!InvNumber as Inv_Number,
InvcHdr!AMount as Inv_Amount;
GROUP BY
PO_Number,
PO_Amount,
Inv_Number,
Inv_Amount;
Will this work as I think or a better way to accomplish this? If we had a PO for each invoice that would make this simple but we dont.
Yes. In a BAQ you can add a UNION ALL subquery:
Epicor checks you have the same number of fields and that the types are the same so you can use Calculated fields to add columns with default values as needed.
Thank you and I will look to build this tonight
