Trying to do a Union All with multiple subqueries

Epicor ERP 10
,
1

Hi Everyone,

I am creating a BAQ that takes my forecasted orders and joins it with my open orders in the system.

Currently, I have a subquery called Forecast that has subquery 3 feeding into it. Subquery 4 then feeds into subquery 3. The problem I am having is that I want to Union All the forecast query that has information from the subqueries with a different subquery. When I run it the syntax checks out but I get an error message showing bad SQL statement. Any advice?

Here is an idea of what my subquery list looks like:
Forecast — TopLevel
(SubQuery3 ---- InnerSubQuery
SubQuery4 ---- InnerSubQuery)
Subquery8 ---- Union All

2

UPDATE: The query is now not pulling in any of the data from the forecast query even though union all.

3

Assuming that you have access to your app server, you should be able to see the actual error in Event Viewer.

Believe its Event Viewer > Applications and Services Log > Epicor App Server

4

What happens if you take a backup, and remove the union subquery. Results the same?

5

Hi Chance,

I will look into the event viewer. If I take a backup and remove the union all subquery, I get the correct number of results with no errors for the forecasted query joined with the other subqueries. It is only when I go to add the union all does it give me problems, then I start to get the wrong number of results and errors. Therefore, the results are not the same. I should have around 4000 rows from forecasted and 1000 from the query I’m trying to union. Without the union, I get the 4000 rows expected.

6

Have you thought about using CTEs? I sometimes find it’s easier to use those instead of Subqueries.

7

I have not, what is the difference between a CTE and the subquery?

8

I figured it out. The forecast join was leading to double some of the entries when it had a union. I changed the subqueries beneath it and was pulling the correct number. To solve the union all, I made the subquery 8 the top level and had the forecast be the union all. Then listed the two other queries below it.