UnionBuilder

UnionBuilder works together with QueryBuilder to create database unions, which can be saved and accessed through SQLManager. A union combines the results of multiple select statements to create a Union table. This is extremely useful when making comparisons. For instance, if you have two queries comparing sales for previous years you could instead create a union with them and have all the results available in one object.

Note

There are a few requirements when creating a union. First, each select statement must have the same number of columns. Additionally, the columns must have similar data types, and be listed in the same order.

Topics

Creating Unions

The Union Builder allows you to create queries with which to build a union, and designate what type of union to create.

To Create a Union

Open Union Builder () from SQLManager.
Select a database object from the menu or click Add ().
QueryBuilder will open.
Create a query.
Repeat steps 2-4. If you choose the same database object again QueryBuilder will select the same fields you used for the previous query.
Select Type from the drop-down menu for the unon.
Click Submit () to view the results.
Click Publish () to save the union.

To Edit Unions

Select the union object in SQLManager and click Edit.
Make any necessary changes to the queries themselves by clicking on the Properties button. To edit the filter directly, click the filter button ().
Click Submit () to view the results.
Click publish () to save the union.

Note

The SQL Query pane at the bottom of Union Builder allows you to view the SQL used to generate your join.
You can change the name of the queries by editing the text in the Label field.

The UnionBuilder Toolbar

The UnionBuilder toolbar provides quick access to commonly used commands in UnionBuilder. Click the icon once to carry out the action represented by that icon.

Icon	Description
	Submits the union SQL and opens QueryBuilder module to view the contents of the Union table.
	Creates a new Union query on the currently selected table or system object.
	View the query in QueryBuilder.
	Duplicates the selected Union query.
	Removes all Union queries
	Moves the currently selected object up one row on the list of objects.
	Moves the currently selected object down one row on the list of objects.
	Saves the current union and publishes the contents to the System Objects node in the Database tab of SQLManager.
Cancel	Exits out of the current union.

Union Types

Union Builder supports four types of unions. Please consider these two tables and queries for the following examples:

ContinuedProducts

Product	ProductType	Num_InStock
iPod	IPO	50
USB Phone Charger	USB	25
iPhone5	IPH	3

Query1
SELECT ProductType FROM ContinuedProducts
Results:

ProductType
IPO
USB
IPH

Union, the default type, shows all the rows returned by the two queries, and hides duplicate rows. Combining the two queries from above with the Union operator we get the following results:

ProductType
IPO
USB
IPH
CAS
VHS

Intersect returns all rows that are common to each select statement. Combining the two queries from above with the Intersect operator we get the following results:

ProductType
IPH

DiscontinuedProducts

Product	ProductType	Num_InStock
Cassette Player	CAS	20
VHS Player	VHS	15
iPhone4	IPH	2

Query2
SELECT ProductType FROM DiscontinuedProducts
Results:

ProductType
CAS
VHS
IPH

Union All is similar to a Union, aside from the fact it it shows duplicate rows. Combining the two queries from above with the Union All operator we get the following results:

ProductType
IPO
USB
IPH
CAS
VHS
IPH

Except only returns rows that are not common to each select statement. Combining the two queries from above with the Except operator we get the following results:

ProductType
IPO
USB
CAS
VHS

Fields Dialog

The fields dialog is used for deleting and reordering fields of a single union item and it accessed by clicking on the Fields hyperlink on a query object.

To Delete a Field

Click the Fields hyperlink on the bottom of the query object.
The Fields dialog will pop up and show you the currently selected fields and the order in which they are currently selected.
Select the field you wish to delete and click delete ().
Repeat step 3 until you have deleted all the desired fields.
Click OK to save the changes.

To Change the Order of the Fields

Click the Fields hyperlink on the bottom of the query object.
The Fields dialog will pop up and show you the currently selected fields and the order in which they are currently selected.
Select the field you wish to move and click either the up () or down () button.
Repeat step 3 until all the fields are in the desired order.
Click OK to save the changes.