Appending Queries

When you need to view and merge multiple queries in a combined results set, there are four query operators that allow you to merge two or more separate queries. For example, you may need to merge the results of amount from sales by item, plus units from state sales by state. The operators and their functions are:

Query Operator	Description
	All distinct rows selected by either query are retrieved. No duplicate rows are retrieved.
	All rows selected by either query, including duplicate rows, are retrieved.
	All distinct rows selected by both queries are retrieved.
	All distinct rows selected by the first query but not the second query, are retrieved. (Oracle database servers refer to the Except operator as “Minus.”)

To specify an evaluation order, you can click Image shows Parentheses icon. to add parentheses to an expression which includes two queries or more. The default evaluation order is left to right.

Note:

If your database supports the Intersection and Except operators, but they are not available in the Operator drop-down list, check the Allow SQL-92 Advanced Set Operations connection preference.

The rules governing the use of these operators are:

The number of columns in the Select clause in both queries must be equal.
The data type returned in the columns in both queries must match.
For example, if Column 1 in the first query is a date, Column 1 in the second query must also be a date.
Note:
Items on the Union line can be repositioned to see the results of different intersections.

To append a query:

Verify data types and associated column(s).
This ensures that you know how to merge data in the second query.
Build the Request line.
Add server and local filters, data functions, and computations to the query as needed.
Select Query, then Append Query.
An Operator drop-down list and a second query tab is added below the Request, Filter, and Sort lines. The drop-down list shows whether the queries are linked by way of a union, a union all, an intersection, or an except.
Build the second query.
To merge multiple queries, select the operator to use from the Operator drop‑down list.
Click Process.
You can have Interactive Reporting generate automatically the join path required by the context of the query by using the automatic join path feature. This feature eliminates the need for you to predefine any join paths, because Interactive Reporting determines the paths. When multiple paths are available, you are prompted for which one to use.