13 Working with Queries

This chapter contains the following topics:

13.1 Understanding Queries

Queries enable you to select fields and QBE columns from a form and add conditions to make the search criteria more specific. The query feature is enabled on find browse, search/select, and power browse forms that have a Find button. Additionally, you can create queries in Data Browser for records in tables and business views.

You can design queries for search criteria that you use frequently, and save them for future use. The queries that you create and save are private queries available only to you. However, a private query can be made public by a system administrator.

13.2 Making Private Queries Public

This section contains the following topics:

  • Understanding query promotion

  • Changing a private query to a public query

13.2.1 Understanding Query Promotion

If desired, you can make a private query public so that others in your organization can use it. To promote a private query, you use the User Overrides application (P98950) to copy the private query and assign *PUBLIC as the user or role.

13.2.2 Changing a Private Query to a Public Query

Enter P98950 in the Fast Path.

Note:

The User Overrides application (P98950) is available on the Microsoft Windows client and the web client.
  1. On the Work With User Overrides form, in the Form QBE column, enter the form number on which the query is based (for example, W01012B).

  2. In the UO Type QBE column, enter AQ, and then click the Find button.

  3. Select the row with the private query that you want to promote to a public query.

  4. Click the Copy button.

  5. On the Copy Overrides form, in the User/Role field, enter *PUBLIC to copy the query to the entire company.

    Note:

    If you do not want a private query to be available to all users, you can enter an individual user ID or a role in the User/Role field.
  6. Click OK to save the query.

13.3 Converting Saved Queries to Enhanced Queries

This section contains these topics:

  • Understanding the query conversion process

  • Converting Saved Queries to Enhanced Queries

13.3.1 Understanding the Query Conversion Process

Enhanced Queries (also referred to as Advanced Queries) use a different format than the Saved Queries that users might have created in previous releases. You can convert the format of the old Saved Queries (UO Type = SQ) to Enhanced Queries (UO Type = AQ) using a conversion process.

This conversion is necessary only if you have query records in the User Overrides application (P98950) with a UO Type of SQ. Alternatively, you can search the UOTY field in the F98950 table for the SQ value.

Conversion Process Objects

The conversion process uses the following objects to change the query UO Type from SQ to AQ:

  • R95SQAQ

    You must run the R95SQAQ batch program (UBE) to convert the queries. The report generated by the batch program displays the number of SQ records in F98950 and F98950D tables that were converted to the new format.

  • D95SQAQPO

    You must use the processing option (PO) template D95SQAQPO to enter any combination of the following three attributes to specify which queries to convert:

    • User ID

      By entering a user ID or role, only the F98950 SQ records with the USER field equal to the User ID specified in the processing option will be converted. The corresponding F98950D SQ records will also be converted.

    • Application Name

      By specifying the application name, only the F98950 SQ records with the OBNM field equal to the application name specified in the processing option will be converted. The corresponding F98950D SQ records will also be converted.

    • Form Name

      By specifying the form name, only the F98950 SQ records with the FMNM field equal to the Form Name specified in the processing option will be converted. The corresponding F98950D SQ records will also be converted.

    If no processing option values are specified, all SQ records will be converted to the new format. If new AQ records with the specified User ID, application name, or form name already exist, the conversion for those records are skipped to avoid replacing the AQ records, which are likely to be more recent than SQ records. Logs will contain a message for each AQ record that was not converted.

  • B95SQAQ

    Business function B95SQAQ is called from R95SQAQ. The business function does the conversion.

  • D95SQAQ

    Data structure D95SQAQ is used by the B95SQAQ business function.

User Override Tables

The conversion updates the following tables:

Table Table Name Conversion Effect
F98950 User Override Table The old Saved Query record has the UOTY field set to SQ. These records are converted to Enhanced Query records with the key UOTY equal to AQ. The old SQ records will NOT be removed after the conversion.
F98950D User Override Table Description The old Saved Query record has the UOTY field set to SQ. These records will be converted to Enhanced Query records with the new key UOTY equal to AQ. The old SQ records will NOT be removed after the conversion.

Saved Query Conditions

With the exception of the Current user AB# and/or Test Search value, all the conditions in the old saved queries will be converted. The logs will display an error message indicating that the current user address book number and/or Text Search condition was not converted.

Once it has been verified that the converted queries are correct, you can delete the old Saved Query records (UO Type = SQ) from the User Overrides application.

13.3.2 Converting Saved Queries to Enhanced Queries

Locate the SQ records that you want to convert by accessing the User Overrides (P98950) application.

  1. On the Work With User Overrides form, enter SQ for the UO Type, and then click the Find button.

    Note:

    You can use any combination of UO Type, User/Role, and Form to locate SQ records. Searching for records with only the UO Type equal to SQ displays all of the SQ records.
  2. If you do not want to convert all SQ records, determine the User ID/Role, application, or form for which you want to run the conversion. You can use any combination of these three values to select the records to be converted.

    If there are no records with a UO Type of SQ, you do not have any records to convert.

Converting SQ Records to AQ Records

Enter BV in the Fast Path to access the Batch Versions (P98305W) application.

  1. On the Work With Batch Versions form, enter R95SQAQ in the Batch Application field and click the Find button.

  2. Click the XJDE0001 Version to convert SQ to AQ batch version.

  3. From the Row menu, select Processing Options.

  4. On the Processing Options form, enter the appropriate information to convert the desired records.

    Note:

    You can leave any or all of the processing option fields blank. If you do not specify a User ID, application, or form name, all SQ records will be converted to AQ records.
  5. Click OK.

  6. On the Work With Batch Versions - Available Versions form, click the Select button.

  7. On the Batch Versions Prompting form, click the Submit button.

    A conversion report is automated generated after the conversion.

  8. Review the conversion report.

    The report lists the number of records in F98950 and F98950D tables that were converted to the new AQ format. Because the old records with UO Type equal to SQ are not deleted by the conversion process, you must delete them manually.

Manually Deleting Saved Query Records

After you confirm that the correct Saved Query records were converted to Enhanced Query records, you can delete the appropriate SQ records from the User Overrides (P98950) application.

Access the P98950 application.

  1. On the Work With User Overrides form, enter SQ for the UO Type, and click the Find button.

    Note:

    You can use any combination of UO Type, User/Role, and Form to locate SQ records. Searching for records with only the UO Type equal to SQ displays all of the SQ records
  2. Click each row with an SQ record that you want to delete, and then click the Delete button.

13.4 Finding Query IDs (Release 9.1 Update 3)

EnterpriseOne assigns a query ID to each query that is created. You need this ID if you want to launch an application using a query from an EnterpriseOne Page, or to create a One View Reporting (OVR) task.

To find a query ID

  1. Navigate to the application on which the query resides.

  2. Click the Add, Edit Queries icon located next to the Query field.

  3. Select a public query from the Query drop-down menu.

  4. Click the About Query icon.

    The query information displays in the About window.

  5. Copy the ID in the Query Information area of the form.

  6. If you need to use this information, for example, to create an OVR task, you can copy this information and paste it in the appropriate field.