Changing a Selection for Variables in a Criteria using PeopleSoft Query Manager
Criteria Overview
Criteria, sometimes also referred to as Selection Criteria, specify what records you wish the Query to return. This is the filter that data runs through: only those records meeting the criteria are selected. The simplest form of criteria compares the data in a field to a constant value:
- INSTITUTION = 'UMSYS'
- ACAD_CAREER = 'UG02'
- GRADUATION_DATE > 01/01/2005
A field in one table may be compared to a field in another table with similar data and the same format:
- A.EMPLID = B.EMPLID
- A.ACAD_CAREER = B.ACAD_CAREER
(Incidentally, this is one of the ways to join two tables together in a query, more on that later.)
There are four components of Criteria:
| Logical |
Represents how the criteria rows will be compared with each other. Blank for the first criteria, defaults to AND for subsequent rows. The other options are OR and NOT. |
| Expression 1 |
Used to specify what you are comparing. Can be either a field or and expression. This is the left side of the criteria. |
| Condition Type |
States how Expression 1 is compared to Expression 2 |
| Expression 2 |
This is what Expression 1 is compared to. It can be a constant, field, expression, subquery, or prompt values. This is the right side of the criteria. |
Back to top
When the query is created with multiple tables or prompts, the Criteria page gets complicated.
Here are the rules on query manager:
-
When the expression 2 does NOT start with colon ( : ) nor table alias, such as "A." or "B.", it is for a variable. You can change the variable as mentioned above. (see the "Variable Selection" in the screenshot below)
-
When the expression 2 starts with colon ( : ), it is for prompt. it is usually automatically added when you add a prompt on "Prompt" page. (see the "For prompts" section in the screenshot below)
-
When the expression 2 starts with table alias, such as "A." or "B.", it is for table join. it is usually automatically added when you joined tables on "Records" page. (see the "Table Join" section in the screenshot below)
Back to top
There are a number of ways to add criteria to your query.
Choice #1 If the criteria is for a field that is being displayed,
then you can add it from the Fields page: Click the funnel icon 
Choice #2 If you are adding criteria for a field that is NOT being displayed in the query results,
add the criteria from the Query page using the funnel icon 
"Use as Criteria" icon:
Choice #3 You can go to the Criteria page and click the Add Criteria button:
After choosing the add criteria button or clicking the funnel, the "Edit Criteria Properties" window will show up. See more information in the next section.
Back to top
When you are adding a criteria, you will be presented with the Edit Criteria Properties page:
If you need to choose a different Record and Field for Expression 1,
select the lookup icon 
and a list like the following will be presented. Select the desired record and field from the list.
Condition Types are used to compare Expression 1 (the left side) to Expression 2 (the right side). Some condition types will change the nature of what is needed on the right side of the Criteria. The Edit Criteria Properties page will change as appropriate. Here's a list of the condition types and what they mean.
| Condition Type |
When it Returns a Row |
| 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. For example, STATE in list ('MA', 'ME', 'VT') |
| 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. |
| is null |
The selected record field does not have a value in it. You do not specify a comparison value (Expression 2) 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 wildcard characters that your database software supports. See your database management system documentation for details.
To use one of the wildcard characters as a literal character (for example, to include a % in your string), precede the character with a \ (for example, percent%). |
The entry for Expression 2 depends on the Condition Type you are using. For the basic 'Equal to', 'Greater Than', 'Less Than' it looks like:
If you're using 'Between' condition type, it will change so that you can enter two values of types Constant, Field or Expressions.
The 'In List' allows you to specify a list of constants or point to a subquery which would return a list.
The 'In Tree' lets you point to one or more nodes on a tree. Click the "New Node List" and search by tree name.
Expand the tree node to where you want to add and select the node and click OK.
The 'Like' condition only allows for constants or prompts:
After finishing adding criteria, go to Criteria page and double check the criteria and logic.
Back to top
Logical Operators (AND, AND NOT, OR, OR NOT) allow you to relate multiple criteria in specific ways. When you specify two or more selection criteria, you need to coordinate the criteria. You can use the Logical column to further define the rows of criteria. The Logical Operator always defaults to AND. The first row of criteria will have a blank operator. When you use multiple criteria, rules of logic apply. The query evaluates criteria that are linked by AND before those linked by OR.
Using these incorrectly will generate quite different results. Consider these examples:
ACAD_CAREER equal to GRAD AND ADMIT_TERM equal to 0410
| EMPLID |
ACAD_CAREER |
ADMIT_TERM |
| AD1003 |
GRAD |
0410 |
| AD1005 |
GRAD |
0410 |
| AD1007 |
GRAD |
0410 |
ACAD_CAREER equal to GRAD OR ADMIT_TERM equal to 0410
| EMPLID |
ACAD_CAREER |
ADMIT_TERM |
| AD5028 |
GRAD |
0430 |
| AD1005 |
GRAD |
0410 |
| AD1084 |
GRAD |
0310 |
| AD1077 |
UGRD |
0410 |
| AD1078 |
UGRD |
0410 |
| AA0012 |
UGRD |
0410 |
| AA0025 |
UGRD |
0410 |
Back to top
Group Criteria controls the order in which query executes the criteria rows. The query evaluates the criteria inside the parentheses before the criteria outside the parentheses. Similar to algebra, the group criteria (x+1)y is indicating that x+1 needs to be solved before multiplying the result by y. Use grouping to funnel data from largest to smallest to expedite search.
Here's an example.
- Click Group Criteria
- Type an open and close parenthesis around the criteria rows to be grouped
- Click OK
Example 2: We want to see prospects in the GRAD career for term 0410, or in the MEDS career for admit term 0450.
Query Results:
Back to top
-
Navigate to Query Manager
- Find the query you would like to change and click "Edit".
- Select the "Criteria" tab and click "Edit" for the criteria you want to change. In this example, we want to change the departments that will show in the report. Click the "Edit" button for DEPTID.
- After clicking Edit, the Edit List dialogue box appears. Click the small magnifier glass and a new window will pop up. If you want to add new value, enter a value on the box and click "Add Value" bottom. If you want to delete an existing value, check the small box next to the value and click "Delete Checked Values." After finishing adding or deleting, click "OK" to close the window.
- If you want to delete the entire criteria completely, use delete icon next to "Edit".
- Save the query using "Save As" with different name.
Back to top