Defining Criteria
Use the Criteria page (QRY_CRITERIA) to view and edit selection criteria for your query statement.
Navigation
Image: Criteria page
This example illustrates the fields and controls on the Criteria page. You can find definitions for the fields and controls later on this page.
|
Field or Control |
Definition |
|---|---|
| Add Criteria |
Click to access the Edit Criteria Properties page, where you can add additional criteria for the query. |
| Group Criteria |
Click to access the Edit Criteria Grouping page, where you can group your criteria logically. |
| Reorder Criteria |
Click to access the Edit Criteria Ordering page, where you can reorder the criteria for your query. |
| Logical |
Any rows after the first row must include either an AND or OR logical value in the Logical column to specify whether you want the rows to meet this criterion in addition to other criteria that you have defined or as an alternative criterion. The first criterion that you define does not have a value in this column. The default for subsequent criteria is AND. |
| Edit |
Click to access the Edit Criteria Properties page, where you can edit the existing criteria for the query. |
Use the Edit Criteria Properties page (QRY_CRITERIA_SEC) to add or edit selection criteria properties for your query statement.
Navigation
Select
Open an existing query.
Select the Criteria tab to access the Criteria page, and click the Add Criteria button.
Alternatively, access the Fields page and click the Use As Criteria link.
Image: Edit Criteria Properties page
This example illustrates the fields and controls on the Edit Criteria Properties page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Field |
Select this option 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 select an appropriate comparison operator from the Condition Type drop-down list. |
| Expression |
Select this option 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 and click the New Expression link to create a new expression, you must then select an expression type. If you are entering an aggregate value, select the Aggregate Function 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 SQL. |
Creating Criteria Based on a Field
To create criteria based on a field:
Select
Create a new query or search for an existing one.
Access the Criteria page, and click the Add Criteria button.
Alternatively, access the Fields page and click the Use As Criteria link. If you accessed the Edit Criteria Properties page by clicking the Use As Criteria link on the Fields page, Query Manager populates the selected field in the Expression 1 field.
In the Edit Criteria Properties page, specify the criteria for the field, and click the OK button to return to the Fields or Criteria page.
You can also use a field from a record as criteria even if you do not select that field for query output.
Creating Criteria Not Based on a Field
To create criteria that is not based on a field:
Access the Edit Criteria Properties page by clicking the Add Criteria button on the Criteria page.
The Edit Criteria Properties page appears enabling you to edit Expression 1 and Expression 2 fields.
In the Choose Expression 1 Type group box, select the Field or Expression option.
Edit the second (right-hand) Expression column to enter comparison values.
Save your query.
The condition type determines how Query Manager compares the values of the first (left-hand) expression to the second (right-hand) expression.
Query Manager also offers a not option that reverses the effect of each condition type. For example, not equal to returns all rows that equal to would not return.
Note: You should use the not version of an operator (for example, not equal or not less than) rather than the NOT operator on the entire criterion (for example, AND NOT or OR NOT logical operator). 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.
|
Condition Types |
Return Values |
|---|---|
|
between |
The value in the selected record field falls between two comparison values. The range is inclusive. |
|
equal to |
The value in the selected record field exactly matches the comparison value. |
|
exists |
This operator is different from the others, in that it does not compare a record field to the comparison value. The comparison value is a subquery. If the subquery returns any data, PeopleSoft Query returns the corresponding row. |
|
greater than |
The value in the record field is greater than the comparison value. |
|
in list |
The value in the selected record field matches one of the comparison values in a list. |
|
in tree |
The value in the selected record field appears as a node in a tree created with PeopleSoft Tree Manager. The comparison value for this operator is a tree or branch of a tree that you want PeopleSoft Query to search. Note: PeopleSoft Query should not use trees that contain a combination of dynamic details and range details. The results returned from trees with this combination of details may be inaccurate. |
|
is null |
The selected record field does not have a value in it. You do not specify a comparison value for this operator. Key fields, required fields, character fields, and numeric fields do not allow null values. |
|
less than |
The value in the record field is less than the comparison value. |
|
like |
The value in the selected field matches a specified string pattern. The comparison value may be a string that contains wildcard characters. The wildcard characters that PeopleSoft Query recognizes are % and _. % matches any string of zero or more characters. For example, C% matches any string starting with C, including C alone. _ matches any single character. For example, _ones matches any five-character string ending with ones, such as Jones or Cones. PeopleSoft Query also recognizes any wild-card characters that your database software supports. See your the documentation for your database management system for details. To use one of the wild-card characters as a literal character (for example, to include a % in your string), precede the character with a \ (for example, percent\%). |
Note: If you select the EFFDT field on an effective-dated table, you can use the effective-date operators in PSQuery.
This topic provides an overview of comparison values and discusses how to:
Select a constant value.
Build a list of values.
Add prompts to an expression list.
Add comparison values to an expression list.
The procedure for entering comparison values differs depending on what kind of value you are entering. If you are comparing one field to another, select the second record field; if you are comparing the rows to a constant value, enter the constant.
The following table describes all the available value types, the pages that appear based on each comparison type, and the fields that you must complete in those pages.
|
Value Type |
Action |
|---|---|
|
Field |
The value in the selected field is compared to the value in another field, usually a field in another record component. When you select Field as the comparison value, the Choose Record and Field page 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. |
|
Expression |
The value in the selected field is compared to an expression that you enter, which PeopleSoft Query evaluates once for each row before comparing the result to the value in the selected field. When you select Expression as the comparison value, the Define Expression page appears. In the text box, enter a valid SQL expression. To add a field or user prompt to the expression, click the Add Field or Add Prompt link, respectively. These links display the same pages that you see when adding a field or prompt as a comparison value: Add Prompt displays the Run-time Prompt page; Add Field displays the Select a Field page. The only difference is that PeopleSoft Query adds the field or prompt to your expression rather than using it directly as the comparison value. |
|
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 page appears. In the text box, enter the value that you want to compare the first expression to. To add a value by selecting it from a list, click the Look Up button to display the Select a Constant page. Note: A list of constants is available only for fields that have translate values or an assigned prompt table. |
|
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 page appears. Click the New Prompt link to move to the Edit Prompt Properties page. To modify an existing prompt, click the Edit Prompt link. See Defining Prompts. |
|
Subquery |
The value in the selected field is compared to the data that is returned by a subquery. When you select Subquery as the comparison value, the Define Subquery page appears. Click the Define/Edit Subquery link to move to the Records tab to start a new query. |
|
In List |
The value in the selected field is compared to a list of values that you enter. This value type is available only when the selected operator is in list or not in list. When you select in list as your comparison value, the Edit List page appears. Use the Look Up button to display the Edit List page and search for the desired values. Note: Values will appear here only if you have entered them previously. |
|
Current Date |
The value in the selected field is compared to the current date on the database server. |
|
Tree Option |
The value in the selected field is compared to a selected set of tree nodes. This value type is available only when the selected operator is in tree or not in tree. When you select Tree Option as the comparison value, the Select Tree Node List page appears. Use this page to create a list of values for PeopleSoft Query to compare to the value from the first expression. |
|
Tree Prompt Option |
The value in the selected field enables you to select a tree value as a tree prompt, when the query is run. This value type is available only when the selected operator is in tree or not in tree. When you select the Tree Prompt Option as the comparison value, all options to select tree values at design time are not available. However, you are able to select tree values as tree prompts when you run the query. |
|
Effective Seq (effective sequence) |
Used on some effective-dated records, the effective sequence is a sequencing number that is provided to further refine the effective date. |
Note: Not all value types are available for all operators. For example, when you select the exists operator, Subquery is the only available value type. After you select an operator, PeopleSoft Query displays only the value types that are available for that operator.
Selecting a Constant Value
When you select Constant in the Choose Expression 2 Type section as your comparison value, the Define Constant section appears. In the Constant field, enter the value to which you want to compare the first expression. Otherwise, click the Constant Look Up button to access the Select a Constant page where you can select a constant value from the available constants.
Note: A list of constants is available only for fields that have translate values or an assigned prompt table.
To select a constant value from the available constants:
Click the Constant Look Up button on the Select A Constant page.
Select the value that you want from the result list by clicking its associated link.
The Select A Constant page appears again with the selected value in the field. If you are working with a date field, you can select a date/constant from a calendar.
If more than one field exists on the Select A Constant page, you must repeat steps 1 and 2 for each field to further narrow your search for the constant.
Click the OK button.
The Edit Criteria Properties page appears with the selected value in the Define Constant section.
Image: Select A Constant page - one field
This example illustrates the field and controls on the Select A Constant page when there is only one constant field.
Image: Select a Constant page - multiple fields
This example illustrates the fields and controls on the Select a Constant page when there are multiple constant fields.
Building a List of Values
When you select In List as your comparison value, the Edit List page appears. Use the Edit List page to build a list of values for PeopleSoft Query to compare to the value from the first expression. (After you have created such a list, you can also use this page to select from the list.)
To add a comparison value to the list, click the Look Up button on the Edit List page, which dynamically reflects which record is used.
Image: Edit List page
This example illustrates the fields and controls on the Edit List page. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| List Members |
Lists the values that have been selected using the Add Value button. Note: The grid, containing the selected value, appears when a value is selected. To delete a value, select the check box to the left of the appropriate List Members value, and click the Delete Checked Values button. |
| Value |
To add a value, enter it into the Value text box and click the Add Value button. The value appears in the List Members grid. To select from a list of values, click the Search button to display the Select a Constant page. Click the Look Up button to display the Look Up page. Enter part of a value in the text box. (The system automatically adds a wild card to the end of the entry, which enables you to do a partial search. For example, if you enter a value of 10, the system returns all values, (up to a total of 300), that begin with 10, such as 10, 100, and 10069.) Click the Look Up button to display the list of values that corresponds to the search criteria. Select the desired value from the list by clicking its associated link. When the Select a Constant page appears again, click the OK button, and the selected value appears in the List Members grid. |
| Add Prompt |
Click to add one or more prompts to the list so that users can enter the comparison values when they run the query. Note: The prompts must be defined before you can select them. If no prompts have been defined, you will receive an error message. |
| OK |
Click to accept the values that are listed in the List Members grid. You will return to the Edit Criteria Properties page, where the selected values are displayed in the Edit List page. |
| Cancel |
Click to return to the Edit Criteria Properties page without saving selections. |
Adding Prompts to an Expression List
You can add one or more prompts to the expression list so that users can enter comparison values when they run a query.
Note: You must have defined the prompts before you can add them to your expression list.
To add prompts to an expression list:
With the expression list open, click the Add Prompt link in the Edit List page to access the Select a Prompt page.
Click the required prompt.
The prompt appears on the Edit List page.
If you selected the in list operator, you may want to add more than one prompt so that your users can enter more than one value to search for.
To add another prompt, click the Add Prompt link again and select a different prompt.
Because you already have a prompt in place, a different page appears showing the prompt that you have already added.
To add the next prompt, click the New button and complete the Run-time Prompt page.
The settings for this second prompt are the same as those that are used with the first prompt. If you want a different label for this prompt, enter that label in the Heading Text text box.
When you click the OK button, the second prompt appears in the Available Prompts list.
Highlight the second prompt, and click the Select button to add it to the list of comparison values.
The prompt then appears in the Edit List page.
Repeat this process for each prompt that you want to add.
When you finish adding prompts, click the OK button to close the Edit List page.
Adding Comparison Values to an Expression List
When you select the Tree option as the comparison value (available if you select the in tree or not in tree condition type), the Select Tree Node List page appears. Use this page to create a list of values for PeopleSoft Query to compare to the value from the first expression. Click the New Node List link to display the Select Tree page.
If the Selected Nodes List has been previously populated, you can either:
Change the tree by clicking the New Nodes List link.
Open the previously selected tree, bypassing the Tree Selection page, by clicking the Edit Node List link.
Click the name of the tree that you want to display the Display and Select TreeNodes page, which you can use to select which element of the tree PeopleSoft Query will check. If no nodes have been previously selected, the Selected Nodes List grid is collapsed. If you do not know the name of the tree, you can perform a search for the tree.
Image: Select a Tree page
This example illustrates the fields and controls on the Select a Tree page.
Image: Display and Select TreeNodes page
This example shows how to select tree nodes in the Display and Select TreeNodes page.
To select tree nodes:
In the Display and Select TreeNodes page, highlight the desired tree node, and click the Add Node icon.
If you know the name of the node that you want, enter the name of the node in the Manual Selection drop-down list.
Alternatively, click the Look Up button to select the desired node from a list of available nodes. When you find the node that you’re looking for, click the Add to List button to add it to the list. The nodes that you enter or select from the list appear in the Selected Nodes drop-down list.
Remove nodes from the list by clicking the Remove from List icon corresponding to the node to be deleted.
Display the selected tree branch by clicking the corresponding icon.
When you are done selecting nodes, click OK.
The selected tree setID, tree name, effective date, and nodes appear in the Select Tree Node List page.
Defining a Criteria with Tree Prompt in PeopleSoft Query
In addition to the option of selecting the tree value at design time for a criteria, Tree Prompts option will allow the user an option to select the tree and node value at run time. This allows the user to reuse the same query to get various organizational reports.
Note: In both web-based Query Manager and Windows client [psqed.exe], while creating a query you have the option of selecting to enter the tree details either at the design time or at the runtime. There is a Tree Prompt Option (an expression type) and a Tree Option when you select the condition type as In Tree or Not In Tree. Based on your selection, the tree option is handled to execute the query accordingly in the specified modules.
When you select the Tree Prompt Option as the comparison value, all options to select tree values at design time are not available. However, you are able to select tree values as criteria prompts when you run query.
Image: Edit Criteria Properties page with the Tree Prompt Option is selected
This is an example of the Edit Criteria Properties with the Tree Prompt Option is selected.
After the Tree Prompt Option is selected:
The Criteria page has a same criteria prompt entry.
The View SQL page displays the viewable SQL for the in tree prompt.
Note: Since tree information at query design time is not known yet, the SQL in the View SQL page is different from the SQL that actually runs in the database. When query is finally executed, based on the user's selection of tree and nodes when prompted, the SQL is modified accordingly with tree info in order to fetch rows of data.
When you access the Run page, you are prompted for selecting the tree for the specified field and its node list.
Based on the tree node list that you selected, and after collecting the other prompt values, if any, the results are displayed on the Run page.
Note: Query that has a criteria with tree prompts cannot be run as part of any process, either via Process Scheduler or from command line. However, you can execute the query with tree prompts from Query Scheduler.
Effective-dated tables have record definitions that include the Effective Date (EFFDT) field. This field, used throughout the PeopleSoft applications, provides a historical perspective enabling you to see how the data has changed over time. Whenever users add a row of data to the table, they specify the date on which that data becomes effective; whenever users change a row of data, they specify a new effective date and the system retains the previous version of the row as history.
When you use a PeopleSoft application for day-to-day processing, you usually want the system to give you the currently effective rows of data—the rows where the effective date is less than or equal to today’s date. You do not want to see the history rows, which are no longer accurate, nor do you want to see future-dated rows, which are not yet in effect.
When you query an effective-dated table, however, you may want to see some rows that are not currently in effect. You might want to see all the rows, regardless of their effective dates. Or you might want to see the rows that were effective as of some date in the past.
To specify effective date criteria:
When you choose the record that has EFFDT as a key field, Query Manager automatically creates default criteria and adds that criteria to the Criteria page.
This criteria is used to specify which row of data PeopleSoft Query retrieves for each item in the table. The default is the currently effective row. Defaults are:
Expression 1: Record Alias.EFFDT
Condition Type: EffDt <=
Expression 2: Current Date
Effective Sequence: Last
If you select one of the comparison options, choose to compare each row’s effective date against today’s date or a date other than today.
Select Current Date to compare each row’s effective date against today’s date.
Select Constant to display the Define Constant box so that you can enter a date.
Select this option when you want to see the rows that were effective as of a past date or that will be effective on some future date.
Select Expression to display the Define Expression page so that you can enter a SQL expression that evaluates to a date.
Select this option if you want to prompt users for an effective date when they run the query. You can add a prompt to the expression that you define in the Define Expression page.
Select Field to display the Select Field box so that you can select the record field that holds the date to which you want to compare effective dates.
Select this option when you want to see the rows that were effective at the same time as some other record. For example, if you’re reviewing the list of products on a customer order, you will want to see the products that were effective on the date of the order.
Select First Effective Date to return the row with the oldest effective date, usually the first row that is entered for an item.
Select Last Effective Date to return the row with the latest effective date, even if that date is still in the future.
Removing the Effective Date criterion is equivalent to selecting No Effective Date. The query returns all rows, regardless of their effective dates.
Note: All options (except No Effective Date) return a single row for each item on the table. If you want a subset of the rows (for example, all future-dated rows or all history rows), enter a selection criterion in the Effective Date field. Use the standard comparison operators rather than the Effective Date comparison operators.
Remember that the effective date operators work differently than the standard comparison operators: they always return a single effective-dated row. For example, Eff Date <= returns the one row for which the EFFDT value is most recent, whereas not greater than would return the currently active row and all history rows.
Using PeopleSoft Query, you can relate multiple criteria in specific ways that you define using the AND, AND NOT, OR, and OR NOT operators. You can also group criteria using parentheses.
This topic describes the AND and OR logical operations and discusses how to group criteria in query.
Understanding the AND and OR Logical Operators
When you specify two or more selection criteria for a query, you must tell PeopleSoft Query how to coordinate the different criteria. For example, suppose you are querying your list of customers and you have defined two criteria: one selects customers from the state of Washington and another selects customers who have purchased airplanes. You may want PeopleSoft Query to return only those rows that meet both conditions (customers in Washington who have purchased airplanes), or you may want the rows that meet either one of the conditions (all Washington customers plus all customers who have purchased airplanes).
Image: Rows returned by AND and OR
This diagram illustrates the rows that are returned by AND and OR.
When your query includes multiple criteria, link them using either AND, AND NOT, OR, or OR NOT. When you link two criteria with AND, a row must meet the first and the second criterion for PeopleSoft Query to return it. When you link two criteria with OR, a row must meet the first or the second criterion, but not necessarily both.
By default, PeopleSoft Query assumes that you want rows that meet all of the criteria that you specify. When you add a new criterion, PeopleSoft Query displays AND in the Logical column on the Criteria tab. To link the criterion using one of the other options instead, select the required option from the drop-down list.
Grouping Criteria in Query
When your query includes multiple criteria, PeopleSoft Query checks the criteria according to the rules of logic: it evaluates criteria that are linked by ANDs before those that are linked by ORs. When all the criteria are linked by ANDs, this order always returns the correct results. When you include one or more ORs, however, this is not always what you want.
Image: Criteria page, example of criteria that returns an incorrect result
This example shows the criteria settings for a list of customers who are not friends and reside in either California (CA) or Florida (FL).
The set of criteria that was entered in the previous sample page returns a list of customers in California (except for those who are friends) and all customers in Florida (including those who are friends). This list results because PeopleSoft Query evaluates criteria in the order of appearance. It looks for rows where the customer is not friend and where the state is California or rows where the state is Florida.
What you really want PeopleSoft Query to search for are rows where the state is California or Florida, and where the customer type is not friend. That is, you want PeopleSoft Query to evaluate the OR before the AND. To accomplish this task, add parentheses to the list of criteria. When a list of criteria includes parentheses, PeopleSoft Query evaluates the criteria inside the parentheses before the criteria outside the parentheses.
Using the Group Criteria button on the Criteria page to access the Edit Criteria Grouping page (QRY_CRITERIA_GROUP) where you can insert the opening parenthesis just before the field name and the closing parenthesis just after the comparison value.
Image: Edit Criteria Grouping page, adding parentheses around the last two criteria
This example shows the Edit Criteria Grouping page with open and close parentheses around the second the third criteria.
Image: Criteria page, parentheses around the last two criteria
This example shows the Criteria page with open and close parentheses around the second the thirst criteria.
To group criteria:
Click the Group Criteria button on the Criteria tab.
The Edit Criteria Grouping page appears.
Use the edit boxes to enter parentheses for the criteria as needed.
A parenthesis appears at the beginning of the Expression1 column for the first row that you selected and at the end of the Expression2 column for the last row that you selected. In the previous example, notice that the AND operator precedes the parentheses, while the OR operator is located within the parentheses.
You can add as many parentheses as needed.