Defining Filters in Composite Queries
Use the Composite Query Manager page - Filters section and the Add / Edit Filter page (PSCPQFILTERDTL_SEC) to view, define, and maintain query filters for the selected output fields in the composite queries.
Navigation
Select Reporting Tools, Composite Query, Composite Query Manager.
Create a new composite query or open an existing one.
In the Composite Query Manager page - Fields section, click the Actions icon and select the Add Filter option from the Action list.
Image: Composite Query Manager page - Filters section
This example illustrates the fields and controls on the Composite Query Manager page - Filters section, including a list of filters that is defined for the composite query. You can use the Action icons in the Filters section to edit filters or remove filters from the composite query.
Image: Add / Edit Filter page
This example illustrates the fields and controls on the Add / Edit Filter page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Condition Type |
Select a value from the Condition Type drop-down list. The available options are greater than, not greater than, less than, not less than, equal to, not equal to, in list, and not in list. |
| Criteria Type |
Select a value from the Criteria Type drop-down list. The available options are:
|
To add filters to composite queries:
Select Reporting Tools, Composite Query, Composite Query Manager.
Open an existing composite query or create a new one.
In the Composite Query Manager page - Fields section, click the Actions icon for the output field that you want to add as a filter and select the Add Filter option.
Alternatively, in the Query Selected section, click the Actions icon for the field that you want to add as a filter and select the Add Filter option.
The Add / Edit Filter page appears, enabling you to create a filter against the selected field.
Select a value from the Condition Type drop-down list.
The available options are greater than, not greater than, less than, not less than, equal to, not equal to, in list, and not in list.
Select the values in the Filter Expression - Right section.
Select a value from the Criteria Type drop-down list.
The available options are Expression, Field, Prompt, and Constant.
Select a value from the Expression 2 Text section.
This value is used for creating the filter with the selected field in the Filter Expression - Left section.
Click the OK button to create the filter.
The Composite Query Manager page - Filter section appears, listing the newly added filter.
Image: Add / Edit Filter page - value in the Criteria Type drop-down list is set to Field
This example illustrates the Add / Edit Filter page and the value in the Criteria Type drop-down list is set to Field. The Expression 2 Text section displays 10 query fields from the base queries EMPLOYEE and JOB. The EMPLOYEE.“A.QE_JOBCODE” field is selected for creating the filter with the previously selected field A.QE.JOBCODE in the JOB query.
Image: Add / Edit Filter page - value in the Criteria Type drop-down list is set to Expression
This example illustrates the Add / Edit Filter page and the value in the Criteria Type drop-down list is set to Expression. The Expression 2 Text section displays all expressions that were defined for the composite query. The %Upper expression is selected for creating the filter with the previously selected field A.QE_EMPLOYEE_NAME in the EMPLOYEE query.
Image: Add / Edit Filter page - value in the Criteria Type drop-down list is set to Prompt
This example illustrates the Add / Edit Filter page and the value in the Criteria Type drop-down list is set to Prompt. The Expression 2 Text section displays all prompts that were defined for the composite query. The :1 = EMPLID - ID prompt is selected for creating the filter with the previously selected field A.EMPLID in the EMPLOYEE query.
Image: Add / Edit Filter page - value in the Criteria Type drop-down list is set to Constant
This example illustrates the Add / Edit Filter page and the value in the Criteria Type drop-down list is set to Constant. The constant _an% finds any employee with the last name containing one or more characters followed by the character an. For example, Andersen, Bannister, and Branham.
Image: Composite Query Manager page - filters are added
This example illustrates the Composite Query Manager page - Filter section after adding field-field, field-expression, and field-prompt filters as defined in the previous examples.
To add a filter for an expression in a composite query:
Select Reporting Tools, Composite Query, Composite Query Manager.
Open an existing composite query.
In the Composite Query Manager page, Expressions section, click the Menu icon for the expression that you want to add a filter to and select the Add Filters option.
Alternatively:
In the Composite Query Manager page, Expressions section, click the Menu icon for the expression to which you want to add a filter.
Select the Add Field option to add the expression to the output field list.
In the Fields section, click the Actions icon for the expression field that you want to add a filter to and select the Add Filter option.
Use the Add/Edit Filters page to select the condition type, criteria type, and field (or prompt, expression, constant) to create the filter.
Image: Add/Edit Filter page
This example illustrates the Add/Edit Filter page displaying the Filter Expression – Left section (the left operand) and the Filter Expression – Right section when you add a filter for an expression field.
Click the OK button to accept the changes and return to the Composite Query Manager page.
The newly added filter is listed in the Composite Query Manager page on the Filters tab.
Specify the filter logical properties as needed.
Save the composite query.
To edit filters in composite queries:
Select Reporting Tools, Composite Query, Composite Query Manager.
Open an existing composite query.
In the Composite Query Manager page, click the Filters button.
The Filters section appears.
Click the Actions icon for the filter that you want to edit and select the Edit option.
The Add / Edit Filter page appears.
Specify filter properties such as condition type, criteria type, and expression 2 text.
Click the OK button to accept the changes.
The Composite Query Manager page - Filter section reappears, listing the filter with the new settings.
To remove filters from composite queries:
Select Reporting Tools, Composite Query, Composite Query Manager.
Open an existing composite query.
In the Composite Query Manager page, click the Filters button.
The Filters section appears.
Click the Actions icon for the filter that you want to remove.
Select the Remove option.
The selected filter is removed from the Filters section.
When your composite query includes multiple filter criteria, you may group filter criteria with parentheses to define how filter criteria should be evaluated. For example, filter criteria inside the parentheses are evaluated before the filter criteria outside the parentheses.
Use the Edit Filter Grouping page (PSCPQFILTERGRP_SEC) to edit the filter grouping in a composite query.
Image: Edit Filter Grouping page
This example illustrates the fields and controls on the Edit Filter Grouping page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Group Selected |
Click this button to add the parentheses around the selected filters. |
| UnGroup Selected |
Click this button to remove the parentheses around the selected filters. |
| Reset All |
Click this button to remove all parentheses that currently exist in the Filters section. |
Steps Used to Group Filter Criteria in Composite Queries
To group the filter criteria in composite queries:
Select Reporting Tools, Composite Query, Composite Query Manager.
Open an existing composite query.
In the Composite Query Manager page, click the Filters button.
Click the Group Filter button to access the Edit Filter Grouping page.
Select the filters that you want to group together.
Image: Edit Filter Grouping page - filters are selected
This example shows the Edit Filter Grouping page. The second and the third filters are selected.
Click the Group Selected button to add the parentheses around the selected filters.
The selected filters are grouped using parentheses.
Click the OK button to apply the changes to the filters.
The Composite Query Manager page - Filter section reappears showing the grouped filters.
Image: Composite Query Manager page - Filter section - filter are grouped
This example shows the Composite Query Manager page - Filter section. The second and the third filters are grouped using parentheses.
