Previous Topic
Creating Union Queries
The Menu > Query > Union option of the Query Editor enables you to combine specified records from more than one query into a result set. Unlike a Join which creates one record with multiple columns from the 2 queries, the Union appends the results of one query to the results of the second query so the number of records is the sum of the two queries. You must match each column of the first query to the second query with the same number of columns, the same SQL types, and the same sequence of columns. The names do not need to match.
A common usage is to build a mash-up query that combines a query from one instance of a database to a second instance of the database. For example you want all the records for table Customers from one database appended to all the records from table Customers from a second database. The two Customers tables must match exactly the number of columns, the SQL type and sequence.
To create a union query:
- In the Catalog Manager, right-click a selected query (here we call it primary query) and select Edit Query to open the Query Editor.
- In the Query Editor, select Menu > Query > Union > New.
- In the Enter Query Name dialog, specify a name for the query and select OK.
- A new Query Editor is displayed. Create the query as required.
Notes:
- When creating a union query, note that the selecting order should refer to the order in the primary query. The SQL type and the number of the selected columns should match those in the primary query. For example, if you select two columns in the primary query, the first one is of Integer type, and the second one is of String type. Then, in the union query, you should also first select an Integer column, and then a String type column.
- A union query cannot support formulas and parameters.
After union queries are created, you can also manage them as follows:
- In the Catalog Manager, right-click the primary query and select Edit Query.
- In the Query Editor, select Menu > Query > Union > Select. The Union dialog appears.
- To add a union to the query, select a union in the Queries box and select
. To remove a union from the query, select it in the Union box, and then select 
. - In the Union box, select the Attribute column to specify the type of the union.
: No duplicate records will be returned.
: All records will be returned.
- When done, select OK to confirm the changes.