Creating a Query Using Having Criteria


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. Click the Create New Query link.

    Step 3
  4. Use the Records page to view existing records. Select an existing record to create a new query.

    Step 4
  5. The first step in creating a query is to find an existing record for the query. In this example, you will locate and use the Service Indicator Data record.

     

    Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "SRVC_IND_DATA"
    .

    Step 5
  6. Click the Search button.

    Step 6
  7. Click an entry in the Add Record column.

    Step 7
  8. The Query page appears, displaying several fields. Use this page to add fields to a query.

    Step 8
  9. Click the EMPLID - EmplID option.

    Step 9
  10. Click the vertical scrollbar.

    Step 10
  11. Click the AMOUNT - Amount option.

    Step 11
  12. Click the Fields tab.

    Step 12
  13. Use the Fields page to view how fields are selected for output, view the properties of each field, change headings, change column and sort orders, and apply aggregate values.

    Step 13
  14. In this example, you first need to apply an aggregate function to the AMOUNT field. Aggregate functions must be assigned before you are able to apply Having criteria.

     

    Click the Edit button.

    Step 14
  15. Use the Edit Field Properties page to customize your field properties.

    Step 15
  16. Click the Sum option.

    Step 16
  17. Click the OK button.

    Step 17
  18. Click the Having tab.

    Step 18
  19. Use the Having page to view any existing Having criteria for the query and, if necessary, to add or modify the Having criteria for the query.

    Step 19
  20. In this example, you are adding the criteria for IDs whose sum is greater than $9.

     

    Click the Add Having Criteria button.

    Step 20
  21. Use the Edit Having Criteria Properties page to edit or define new having criteria for the query.

    Step 21
  22. Click the Select Record and Field button.

    Step 22
  23. Click the A.AMOUNT - Amount link.

    Step 23
  24. Click the Condition Type list.

    Step 24
  25. Click the greater than list item.

    Step 25
  26. Click in the Constant field.

    Step 26
  27. Enter the desired information into the Constant field. Enter

     a valid value
     e.g.
     "9"
    .

    Step 27
  28. Click the OK button.

    Step 28
  29. Next, save the query.

     

    Click the Save As link.

    Step 29
  30. Use the Enter a name to save this query: page to specify a name and description for the new query you created.

    Step 30
  31. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "SINDSUM"
    .

    Step 31
  32. Click in the Description field.

    Step 32
  33. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "Service indicator fees"
    .

    Step 33
  34. Use the Query Type field to specify the type of query as User, Process, or Role. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types. For the exercise, retain the default query type.

    Step 34
  35. Use the Owner field to specify the access to this query. Private indicates that only the user ID that created the query can open, run, modify, or delete the query. Public indicates that any user with access to the records used by the query can run, modify, or delete the query. For this example, you want to make it a private query.

    Step 35
  36. Click the OK button.

    Step 36
  37. Click the Run tab.

    Step 37
  38. Use the Run page to view your query results.

    Step 38
  39. The results display a list of IDs whose sum amount is greater than $9.

    Step 39

You have successfully defined Having criteria for a query.

Table of Contents  Start Topic