Changing Selections in PeopleSoft Query for Variables in Criteria

peoplesoft DARTS QUERY PSQUERY

This article provides inofrmation on using PeopleSoft® Query Manager to change a Selection for Variables in Criteria.

Detailed Instructions

INFO: PeopleSoft® Version Notice - Depending on the version of PeopleSoft you are using, your screens may look slightly different than some of the images shown on this page, however the basic instructions are the same.

Table of Content

Criteria Overview
How to read Criteria
Using Criteria
Expressions and Condition Types
Logical Operators AND/OR
Grouping Criteria with Parentheses
Step-by-Step Example: Creating and Deleting Criteria

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:
providing an example of what the Criteria tab looks like in Query Manager.

Criteria Element	Description
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.

How to read Criteria

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)

image providing an example of the criteria screen, with Table Alias, Variable Selection, For Prompts and Table Joins highlighted.

Using Criteria

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 showing the Query Criteria Funnel Icon

Highlighting the Add Criteria Option on Fields Tab Page

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 showing the Query funnel icon "Use as Criteria" icon:

showing the location of the use as criteria column on the Query Page.

Choice #3 You can go to the Criteria page and click the Add Criteria button:

showing the location of the Add Criteria Button on the Criteria tab.

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.

Expressions and Condition Types

When you are adding a criteria, you will be presented with the Edit Criteria Properties page:

showing the edit Criteria Properties Dialogue Box.

If you need to choose a different Record and Field for Expression 1,
select the lookup icon showing the lookup magnifying glass icon and a list like the following will be presented. Select the desired record and field from the list.

showing the Select a Field dialogue box in Query Manager.

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:

showing the Condition Type selection pop up box with "less than" selected as the condition type.

If you're using 'Between' condition type, it will change so that you can enter two values of types Constant, Field or Expressions.

showing the Condition Type selection pop up box with "between" selected as the condition type.

The 'In List' allows you to specify a list of constants or point to a subquery which would return a list.

showing the Condition Type selection pop up box with "in list" selected as the condition type.

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.

showing the Condition Type selection pop up box with "in tree" selected as the condition type.

The 'Like' condition only allows for constants or prompts:

showing the Condition Type selection pop up box with "like" selected as the condition type.

After finishing adding criteria, go to Criteria page and double check the criteria and logic.

Logical Operators AND/OR

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.

showing the Logical column with the Operators "And / Or" selected.

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

Grouping Criteria with Parentheses

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.

showing the Edit Criteria Grouping Screen with GRAD career grouping and MEDS career grouping shown.

Query Results:

showing the Query Results of grouping using parenthesis.

Step-by-Step Example: Creating and Deleting Criteria

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.