How do I create a query with a union?
Introduction, title, and music.
To begin, create a new analysis. Choose the Workforce Management > Worker Assignment Real Time subject area.
Click Create.
Click Analysis.
Search for Worker Assignment.
Click Workforce Management > Worker Assignment Real Time.
Now select the columns you want to include. You might need to widen the columns to view the whole column name.
Collapse Catalog.
Expand the Worker subject area.
Click Person Number, Name, and Assignment Status Type Code.
Create a filter so only active employees are included in the report.
In the Assignment Status Type Code column click Settings > Filter.
In the Operator field select is not equal to/is not in.
In the Value field select Inactive.
Click OK.
Now that the filter is created, we don’t have to see this column anymore. The filter will still work even though we no longer are viewing the column.
Click Settings.
Click Delete.
Highlight that the filter is still there.
This shows a report of all active workers.
Click the Results tab.
Enable Content Paging to enable scrolling by page in the report.
Click the Table Properties icon.
Click Content paging.
Click OK.
Return to the Criteria tab and combine this table with another subject area.
In the Selected Columns table click the Combine icon.
Add the Compensation > Salary Details Real Time Subject Area.
Click Compensation > Salary Details Real Time Subject Area.
The first query is here.
Highlight the first query and the Worker area to show the columns that are selected.
Note that for the second query – Salary Details Real Time – the columns have dashed lines around them. This indicates that we need to pull in these fields.
Highlight the second query and the columns below.
In Subject Areas, click the Person Number and Name fields to add them to the second query.
We'll add two filters. The first will filter on active employees.
Click the Filter icon.
Click More Columns.
Expand Worker.
Click Assignment Status Type Code.
Click OK.
In the New Filter dialog:
In the Operator field select is not equal to/is not in.
In the Value field select Inactive.
Click OK.
The second filter will be the “Number of Days Since Last Salary Change” with a date range of 366 days.
Click the Filter icon.
Expand Salary.
Click Days Since Last Salary Change.
Click OK.
In the New Filter dialog:
In the Operator field select is less than or equal to.
In the Value field select 366 (one year).
Click OK.
You can choose Union, Union All, Intersect or Minus. If you choose straight Union, it will show all current workers, which includes workers who have and have not had their salary changed in the last year. Leave this at Union and return to the Results tab.
In the Result column click the Union icon.
Display the options but do not change them.
Click the Results tab.
This report isn't all that useful, but if you change the union to minus, you will get all current workers, minus those who have had a salary change in the last year.
Click the Criteria tab.
Click the Union icon.
Click Minus.
Click the Results tab.
Now this report shows all worker that have not had a salary change in the last year. Thanks for watching.
Oracle copyright, music.