Creating and Saving a Query


Concept


Steps:

  1. Begin by navigating to the Records page.

     

    Click the Reporting Tools link.

    Step 1
  2. Click the Query Manager link.

    Step 2
  3. Use the Query Manager search page to:

    •  Search for an existing query.

    •  Create a new query.

    •  Run queries.

    •  Rename queries.

    •  Export queries.

    •  Copy queries to users.

    •  Organize queries in folders.

    •  Delete queries.

    Step 3
  4. In this example, you want to create a new query.

     

    Click the Create New Query link.

    Step 4
  5. Use the Records page to select the records on which to base the new query.

    Step 5
  6. Using the Records page, you should note that:

    • The Records page appears after you click the Create New Query or the New Query links.

    • The Records search page provides basic and advanced search options.

    • You have to click the Search button to display a list of records based on the search criteria that is entered.

    • You must select at least one record to create a query.

    Step 6
  7. If you know the entire record name, description, access group name, or field name included in the record:

    1.   Select the appropriate option in the Search By field.

    2.   Enter the name in the begins with field.

    3.   Click the Search button to display a list of records that match your search criteria.

    Step 7
  8. You can perform an advanced search by clicking the Advanced Search link.

    Step 8
  9. If you want to view a list of available records, leave the begins with field blank and click the Search button to display a list of up to 300 records.

     

    You can perform a partial search by entering part of the name in the begins with field.

    Step 9
  10. Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "PSOPRDEFN"
    .

    Step 10
  11. Click the Search button.

    Step 11
  12. By default, only the first 20 records appear on the page.

    Step 12
  13. Use the Show Fields link to display the record’s fields.

     

    You can use this information to verify whether you want to base the query on this record.

    Step 13
  14. The Add Record link enables you to access the query fields, from which you can select which fields from the selected record to add to the query.

     

    Click an entry in the Add Record column.

    Step 14
  15. Use the Query page to add fields to the query content.  You can also add additional records by performing joins.

    Step 15
  16. If you have selected the record for an effective-dated table, a message informs you that an effective-date criteria has been automatically added for this record.

     

    If that message appears, click the OK button to close the message.

    Step 16
  17. Use the AZ button to sort fields.

    Step 17
  18. Use the Hierarchy Join link to join parent-child records.

    Step 18
  19. Use the Delete button (minus sign) to delete the displayed record.

    Step 19
  20. Use the Check All button to select all of the fields in the record.

    Step 20
  21. Use the Uncheck All button to clear all selected fields.

    Step 21
  22. The Primary Key Field icon identifies the key fields in a record.

    Step 22
  23. The Join link identifies related-record joins by using record prompts.

    Step 23
  24. Use the Add Criteria button to filter data from the query.

    Step 24
  25. Select the Fields check boxes to add fields to your query.

    Step 25
  26. Click the User ID option.

    Step 26
  27. Click the Description option.

    Step 27
  28. Click the EmplID option.

    Step 28
  29. Click the Row Security Permission List option.

    Step 29
  30. Click the Fields tab.

    Step 30
  31. Use the Fields page to:

    •  View how fields are selected for output.

    •  View the properties of each field.

    •  Change headings, order-by numbers, and aggregate values.

    Step 31
  32. Use the Reorder/Sort button to access the Edit Field Ordering page.

     

    Click the Reorder / Sort button.

    Step 32
  33. Use the Edit Field Ordering page to change the column order and/or sort order for multiple fields.

    Step 33
  34. Use the Descending check boxes to sort fields in descending order.

    Step 34
  35. Use the New Column field to enter the new column number to reorder the columns.

     

    Columns that are blank or assigned a zero are automatically assigned a number.

     

    Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "1"
    .

    Step 35
  36. Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 36
  37. Click the OK button.

    Step 37
  38. Click any Edit button to access the Edit Field Properties page.

     

    Click the Edit button.

    Step 38
  39. Use the Edit Field Properties page to format the query output. For example, to change column headings or display translate table values in place of codes.

    Step 39
  40. Use the Heading section to define a column heading using the following options:

    •  No Heading: The column does not have a heading.

    •  Text: The column heading is the text that you enter in the text box.

    •  RFT Short: The column heading is the short name from the record definition.

    •  RFT Long: The column heading is the long name from the record definition.

    Step 40
  41. Click the Text option.

    Step 41
  42. Use the Heading Text field to assign the heading that appears at the top of the column for the query output for each field listed.

     

    Enter the desired information into the Heading Text field. Enter

     a valid value
     e.g.
     "Employee ID"
    .

    Step 42
  43. Click the OK button.

    Step 43
  44. Click the View SQL tab.

    Step 44
  45. Use the View SQL page to view the underlying SQL code that Query Manager generates based on your query definition.

     

    Note that you cannot modify SQL on this page.

    Step 45
  46. You can save a query at any time after you have selected one record and at least one field for it.

     

    Save queries from any Query Manager pages (except for the Run page) by clicking either the Save button or the Save As link.

     

    Click the Save As link.

    Step 46
  47. Use the Enter a name to save this query as page (also called the Query Properties page) to view and edit data about the current query, such as the query name and description.

     

    This page is also used to record information about your query, so that you can use it again in the future.

    Step 47
  48. Use the Query field to enter a short name for the query.

     

    Query names are uppercase and can be up to 30 characters. You cannot have spaces or any special characters except an underscore.

     

    Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "DEMO_NEW_QRY"
    .

    Step 48
  49. Use the Description field to enter an appropriate description for the query.

     

    A description can:

    •  Be up to 30 characters.

    •  Be mixed case.

    •  Include special characters.

     

    Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "Demo how to create query"
    .

    Step 49
  50. Use the Owner field to select whether your query is:

    •  Private: Only the user ID that created the query can open, run, modify, or delete the query.

    •  Public: Any user with access to the records used by the query can run, modify, or delete the query.

    Step 50
  51. Click the OK button.

    Step 51
  52. Click the Run tab.

    Step 52
  53. Use the Run page to view the results of your query to verify that your query yields the results that you need.

    Step 53

You have successfully created and saved a query.

Table of Contents  Start Topic