Defining Expressions in Composite Queries

Use the Edit Expression Properties page (PSCPQEXPR_SEC) and the Composite Query Manager page - Expressions section to view, add, and maintain expressions in the composite queries.

Image: Edit Expression Properties page for Composite Query

This example illustrates the fields and controls on the Edit Expression Properties page. Definitions for the fields and controls appear following the example.

Image: Composite Query Manager page - Expressions section

This example illustrates the fields and controls on the Composite Query Manager page. The Expressions section lists the defined expressions for the current composite query.

In the Composite Query Manager page, Expressions section, four actions are available from the Action icon:

• Add Field: Use this option to add the expressions to the output field list in the Fields section.

• Add Filter: Use this option to add the expression as a left operand in filter criteria.

• Edit: Use this option to edit the current expression properties.

• Remove: Use this option to delete the expression from the composite query.

Composite Query can expand the aggregate of fields in expressions using the aggregate function that is specified for the selected fields.

If a composite query has field F1, field F2, and expression field F1+F2, then Composite Query expands the expression field as SUM(F1) + AVG(F2) when field F1 has aggregate function SUM and field F2 has aggregate function AVG. Instead of users specifying the field names as shown in SQL to satisfy this requirement, Composite Query can parse the expression for the fields using the following syntax:#{<QueryAlias>.<QueryUniqueFieldName>}.

To display distinct output results that do not list the same field more than once, add the column number to the previous syntax: #{<QueryAlias>.<QueryUniqueFieldName>:<column number>}.

Examples of using the previous syntax are:

• #{Q1.A.SALARY}

• #{Q2.SET ID}

• #{Q2.SET ID:3}

Note that:

• The column number is optional.

  If you do not specify the column number, then Composite Query uses the first field that matches the field name to check if an aggregate function is defined for that field. If so, Composite Query uses the aggregate function to expand the field in an expression. You are not able to use the field without aggregation if it is included in the output field list.

• If you create the composite query using Composite Query Manager, you should remove the expression field that has a derived aggregate from the Group By clause.

  Otherwise, the SQL will not generate the correct Group By clause.

• If you modify the composite query using API, you must set the GroupByNumber property of all fields to ensure that the correct Group By number is used for all output fields.

• If you do not want the derived aggregate for a field in expression, specify zero for the column; for example, #{Q2.SET ID:0}.

To add new expressions 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, click the Actions menu link and select the Add Expression option.

   The Edit Expression Properties page appears.

4. Specify expression properties such as expression type, length, decimals, and text.

5. Click the OK button to create the new expression.

   The Composite Query Manager page reappears. The newly created expression is added to the Expressions section.

6. Optionally, use the Action icon in the Expressions section to add the expressions as the output fields, edit the current expressions, or remove the current expressions from the composite query.

To edit expressions 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 - Expressions section, click the Actions icon for the expression that you want to edit.

4. Select the Edit option.

   The Edit Expression Properties page appears.

5. Specify expression properties such as expression type, length, decimals, and text.

6. Click the OK button to accept the changes.

   The Composite Query Manager page reappears displaying the modified expression based on your selections.

To remove expressions from composite queries:

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

2. Search for and open an existing composite query.

3. In the Composite Query Manager page - Expressions section, click the Actions icon for the expression that you want to remove from the composite query.

4. Select the Remove option.

   A warning message appears.

5. Click the OK button.

   The Composite Query Manager page reappears. The selected expression is removed from the Expression section.

To add expressions to the output field list:

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 - Expressions section, click the Actions icon for the expression that you want to add to the output field list.

4. Select the Add Field option.

   The expression is added to the Fields section, which displays a list of all output fields.

Image: Composite Query Manager page - expression is added as an output field

In this examples, expression %Upper(%Substring(EMPLOYEE.’A.QE_EMPLOYEE_NAME”,7,5)) is added to the Fields section.