I am having a little bit of trouble understanding how to create a union Query. I have 2 queries with 5 fields each. The first 3 are the same field names in both queries except the last 2 fields but they are the same type. From my understanding in Googling for answers I need to have one of these queries as the Top Level and then have the other query set to Union. Only problem is when I have it set this way when I go to pull the Union query in it does not show up on the left to drag it in. If I set it to inner subquery it shows up. My purpose is to join these 2 tables together as one so that I have 5 fields when done.
So if I understand correctly. I have one query that is an inner Subquery (lets just call it query A) Then I have another inner subquery called Query B. They have both the same number of fields and types. I can create a union Query called Union where I join A and B. Then I create a Top Level query and just pull query A in and it should show the combination of both?
At that point, you were done. No need to drag it in. (there’s nothing to join, it just stacks them on top of each other) If you run it, you should see both queries rows in the results. To test, you can either limit both queries to something where you know they are different, or add a calculated field in both where you hard code which level it came from.
There is subquery list panel, that shows all subqueries. They will be concatenated in order (except inner subquries). So you place them for example as Top, Union, UnionAll (BTW UnionALL is more efficient than Union). Or CTE, UnionAll, Top, etc.
Do you have a copy of the ICE Tools User Guide from Epicor?
There are some good/simple examples that helped me decipher those “understood by Epicor” parts.
Okay. So I got it to work. I started off with an inner subquery to get my data in which the firs 3 colums were all the same. Then I added a total of 10 columns with each group of 2 columns having the same data. Then I created 5 queries and used the first 3 columns in each and then added 2 columns to each until I had all of them the same. Then I set the first one to Top and the rest to union and Bingo it worked. Thanks to all.
