Using Join, Union, Intersect, and Except

All query items in a single query must be obtained from only one namespace. You can use these Cognos query objects to merge data from multiple namespaces into a larger result set for aggregation:

• Join
• Union
• Intersect
• Except

For example, creating a report that compares Forecasted Sales against Actual Sales requires data from the Actual Results namespace and the Scheduling namespace. To merge the data between namespaces, create a Union containing two queries: Forecast and Actual. This table shows the content of these queries:

Forecast Tabular Model	Actual Tabular Model
[Scheduling].[Store].[Store Name]	[Actual Results].[Store].[Store Name]
[Scheduling].[Forecasts].[Calls]	Calculation column, named “Calls”, with a numeric value of zero.
Calculation column, named “Volume”, with a numeric value of zero.	[Actual Results].[Actual Results].[Volume]

In the Union, verify that the Duplicates property is set to preserve, which is equivalent to the SQL command UNION ALL. This setting ensures that all values are included in the report. If the Duplicates property is set to Remove, Cognos removes duplicate values at the database level, which reduces overall performance. Setting the Duplicates property to Remove is the equivalent to the SQL command UNION.

For more details on how to create the report described above, see ftt1522869161462.html#xor1475662033646.

Cognos query objects can be nested to create additional layers of relationships between queries. This is necessary to use more than two namespaces to be used in the same report.