Union SET operators
The union sub query (SET operators)allows users to combine the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types to join them. By default, duplicate records are not displayed. So if the row in the sub query matches the row in the master query, it is not displayed.
If you need duplicate records to be displayed, select the Show Duplicate Records option available when creating the Union sub query.
The following video describes an example of a union sub query
Setting up a Union sub query
A good use case for using a union query is when you need to combine multiple fields into a single column from the same or different views. We need to create a master query and then a union sub query to view the combined results of the two queries.
In the following example, we will create a report that displays the number of incident and change requests based on the Assignee Support Company.
To create a union sub query:
- From the BMC Remedy Smart Reporting console, choose Create > Report.
- Select the Drag and Drop Builder, and then select Incident Management from the available Data Sources.
- The Master Query includes two Incident Management fields - Assignee Support Company and Number of Incidents.
Drag and drop these fields to the Columns section.
Optionally, you can add a label field to the master query. In our example, since we are not displaying ticket IDs, we will add a label field to identify the incident and change record numbers. On the Data tab of the report, create the + sign on the bottom panel of the fields to create a label field.
On the Calculated Field dialog box, select the following options:
Field name Value Calculated Field Name Name for the text field we are creating. In our example, enter Label. Formula Type Choose Simple, since we only want to add a simple text field. Formula box Enter Number of Incidents in the text box under the Formula box, and click + Add.
- Click Save.
- Drag and drop the Label field to the Columns section to complete the master query.
- Change the Analysis Style to No Drill.
- Click on the + sign under Master Query to add a Sub Query.
- On the Sub Query Type panel, under Type choose Union.
- From the style options, choose Advanced, and then select AR System from the Data Source, and Change Management from the Views list. Click Ok.
The Advanced option allows us to choose a different view for our sub query. Use the Basic option if your sub query includes fields from the same view as the master query. For example, you can create a master query to view submitted incidents, and a sub query to view the number of resolved incidents.
- On the sub query data view, add a label field for the Change Management view, as described in Step 4.
Then link the Master Query fields to the Sub Query fields. Link the following fields for our example:
Master Query Field Sub Query Field Label Label Assignee Support Company Assign Support Company Number of Incidents Number of Changes
- Select the Show Duplicate option to view duplicate results.
By default, duplicate records are not displayed.
- Click Save to save the sub query.
Your report will show the number of Change and Incident requests for each Assigned Support Company.
If you want additional filters to each query, you can drag and drop these filters to the Sub Query Fields box.