Defining HAVING Criteria in Composite Queries

Use the Composite Query Manager page - Having section and the Add / Edit Having page (PSCPQFILTERDTL_SEC) to view, define, and maintain the Having criteria that are defined for the composite query.

Navigation

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Create a new composite query or open an existing one.

  3. In the Composite Query Manager page - Fields section, click the Actions icon for the aggregate field that you want to add the Having criteria.

  4. Select the Add Having option.

    The Add / Edit Having page appears, enabling you to create Having criteria against the selected field.

Image: Add/Edit Having page for Composite Query

This example illustrates the fields and controls on the Add / Edit Filter page and the value in the Criteria Type drop-down list is set to Field. The EMPLOYEE."EXPR1_5_1” field is selected for creating the having criteria with the previously selected field A.QE_EMPLOYEE_NAME in the EMPLOYEE query.

Add/Edit Having page for Composite Query

Field or Control

Definition
Condition Type

Select a value from the drop-down list. The available options are equal to, greater than, less than, not greater than, not less than, and not equal to.
Criteria Type

Select a value from the Criteria Type drop-down list. The available options are:

  • Expression: If you select this option, the Expression 2 Text section displays a list of the expressions from the base queries.

  • Field: If you select this option, the Expression 2 Text section displays a list of the query fields from the base queries.

  • Prompt: If you select this option, the Expression 2 Text section displays a list of the query prompts from the base queries.

Image: Composite Query Manager page - Having section

This example illustrates the fields and controls on the Composite Query Manager page - Having section after adding Having criteria. You can use the Action icon in the Having section to edit or remove the Having criteria from the composite query.

Composite Query Manager page - Having section

To add Having criteria to composite queries:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Open an existing composite query or create a new one.

  3. In the Composite Query Manager page - Fields section, click the Actions icon for the aggregate field that you want to add Having criteria.

  4. Select the Add Having option.

    The Add / Edit Having page appears.

  5. Select a value from the Condition Type drop-down list.

  6. Select a value in the Filter Expression - Right section.

  7. Select a value from the Criteria Type drop-down list.

    The available options are Expression, Field, and Prompt.

  8. Select a value from the Expression 2 Text section. This value is used for creating the Having criteria with the selected field in the Filter Expression - Left section.

  9. Click the OK button to create the Having criteria.

    The Composite Query Manager page - Having section reappears listing the newly added Having criteria.

To edit Having Criteria in composite queries:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Open an existing composite query.

  3. In the Composite Query Manager page, click the Having button.

    The Having section appears.

  4. Click the Actions icon for the Having criteria that you want to edit and select the Edit option.

    The Add / Edit Having page appears.

  5. Specify the Having properties such as condition type, criteria type, and expression 2 text.

  6. Click the OK button to accept the changes.

    The Composite Query Manager page - Having section reappears, listing the Having criteria with the new settings.

To remove Having criteria from composite queries:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Open an existing composite query.

  3. In the Composite Query Manager page, click the Having button.

    The Having section appears.

  4. Click the Actions icon for the Having criteria that you want to remove and select the Remove option.

    The selected Having criteria is removed from the Having section.

When the composite query has multiple having criteria, you may group them using the parentheses to define how they should be evaluated. For example, the Having criteria inside the parentheses are evaluated before the ones outside of the parentheses.

Use the Edit Having Criteria Grouping page (PSCPQHAVINGGRP_SEC) to edit the Having criteria grouping in a composite query.

Image: Edit Having Criteria Grouping page

This example illustrates the fields and controls on the Edit Having Criteria Grouping page. Definitions for the fields and controls appear following the example.

Edit Having Criteria Grouping page

Field or Control

Definition
Group Selected

Click this button to add the parentheses around the selected Having criteria.
UnGroup Selected

Click this button to remove the parentheses around the Having criteria.
Reset All

Click this button to remove all parentheses that currently exist in the Having section.

Steps Used to Group Having Criteria in Composite Queries

To group Having criteria in composite queries:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Open an existing composite query or create a new one.

  3. In the Composite Query Manager page, click the Having button.

  4. Click the Group Having button to access the Edit Having Criteria Grouping page.

  5. Select the Having criteria that you want to group together.

  6. Click the Group Selected button to add parentheses around the selected Having criteria.

    The selected Having criteria are grouped together using parentheses.

  7. Click the OK button to apply the changes to the Having criteria.

    The Composite Query Manager page - Having section reappears, displaying the Having criteria grouped as specified.