Trying to do a Union All with multiple subqueries


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

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

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

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

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.

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

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

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.