Entering Selection Criteria


Concept


Steps:

  1. The PROSPROG query is displayed. Before adding criteria to the query, run the query result to see how many rows are returned.

     

    Click the Run tab.

    Step 1
  2. The query result displays all 24 rows in the table. This is because no criteria are defined for this query currently. However, you only want to see specific records. To do this, you need to create criteria for specific fields.

    Step 2
  3. Click the Criteria tab.

    Step 3
  4. Use the Criteria page  to view any existing criteria for your query, and if necessary, add or modify selection criteria for the query. In this example, you need to add criteria to the query.

    Step 4
  5. Click the Add Criteria button.

    Step 5
  6. Use the Edit Criteria Properties page to define the selection criteria for the query. First, you need to select the expression to be used as a comparison value.

    Step 6
  7. Select the first expression type in the Choose Expression 1 Type group box:

    Field: Select if you want to base the selection criterion on another field’s value. Usually a field in another record component. To compare the values from fields in two records, you must join the record components. When you select this option, you must go on to select a condition type.

    Expression: Select if you want PeopleSoft Query to evaluate an expression that you enter before comparing the result to the value in the selected field. When you select this option, you must go on to select an expression type. If you are entering an aggregate value, select the Aggregate Expression check box. You can also enter parameters for length and decimal positions. Also enter the expression in the text box. Query Manager inserts this expression into the Structured Query Language (SQL).

     

    In this example, use the default selection.

    Step 7
  8. In the Expression 1 group box, specify the field you want to use as criteria.

     

    In this example, you need to retrieve information about a Campus; therefore, locate the A.CAMPUS - Campus field.

     

    Click the Select Record and Field button.

    Step 8
  9. Click the A.CAMPUS - Campus link.

    Step 9
  10. The Condition Type determines how PeopleSoft Query compares the values of the first expression to the second expression.

     

    The available condition types are: between, equal to, exists, greater than, in list, in tree, is null, less than, and like. For each of the condition types, Query Manager offers a “not” option that reverses its effect. For example, not equal to returns all rows that equal to would not return.

     

    Note that it is always better to use the not version of an operator rather than the NOT operator on the entire criterion. When you use NOT, PeopleSoft Query cannot use SQL indexes to speed up the data search. When you use the not version of an operator, PeopleSoft Query can translate it into a SQL expression that enables it to use the indexes.

    Step 10
  11. In this example, you want to display a campus that is equal to MAIN. Therefore, you will leave the condition type as equal to.

    Step 11
  12. The procedure for entering comparison values differs depending on what kind of value you are entering. You use the Choose Expression 2 Type group box to define the second type of expression.

    Step 12
  13. If you select Field, the value in the selected field is compared to the value in another field, usually a field in another record component.

     

    When you have selected Field as the comparison value, the Choose Record and Field dialog box appears. The Record Alias field lists all the records that are part of the current query. Select the record and the field. The selected field name appears in the second Expression column of that field's row.

    Step 13
  14. If you select Expression, the value in the selected field is compared to an expression you enter, which PeopleSoft Query evaluates once for each row before comparing the result to the value in the selected field.

     

    When you have selected Expression as the comparison value, the Define Expression dialog box appears. In the text box, enter a valid SQL expression.

     

    To add a field or user prompt to the expression, click the Add Prompt link or the Add Field link. These links display the same dialog boxes that you see when adding a field or prompt as a comparison value: the Add Prompt displays the Run-time Prompt dialog box; the Add Field link displays the Select Record and Field dialog box. The only difference is that PeopleSoft Query adds the field or prompt to your expression rather than using it directly as the comparison value.

    Step 14
  15. If you select Constant, the value in the selected field is compared to a single fixed value.

     

    When you select Constant as the comparison value the Define Constant dialog box appears. In the text box, enter the value you want to compare the first expression to. To add a value by selecting it from a list, click the lookup button to display the Select a Constant page.

    Step 15
  16. If you select Prompt, the value in the selected field is compared to a value that you enter when running the query.

     

    When you select Prompt as the comparison value, the Define Prompt dialog box appears. Click the New Prompt link to move to the Edit Prompt Properties page.

    Step 16
  17. If you select Subquery, the value in the selected field is compared to the data returned by a subquery.

     

    When you select Subquery as the comparison value, the Define Subquery dialog box appears. Click the Define/Edit Subquery link to move to the Records tab to start a new query.

    Step 17
  18. In this example, you are going to select a specific value, so you will use the default Constant option.

    Step 18
  19. Next, specify the campus value for which you are looking.

     

    Click in the Constant field.

    Step 19
  20. Enter the desired information into the Constant field. Enter

     a valid value
     e.g.
     "MAIN"
    .

    Step 20
  21. Click the OK button.

    Step 21
  22. Click the Save button.

    Step 22
  23. View the results of the query.

     

    Click the Run tab.

    Step 23
  24. The results display prospects for the MAIN campus.

    Step 24

You have successfully created a query with criteria properties.

Table of Contents  Start Topic