Begin by navigating to the Records page.
Click the Reporting Tools link.Step 1
Click the Query Manager link.Step 2
Click the Create New Query link.Step 3
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 ADDRESSES record.
Enter the desired information into the begins with field. Enter
Click the Search button.Step 5
Use the Records page to view existing records. Select an existing record to create a new query.Step 6
Click the Add Record link.Step 7
A dialog box appears that indicates that the effective date criteria has been automatically added to this effective dated record.
Click the OK button.Step 8
The Query page lists all the fields for the selected record. You use this page to select the fields that you want to use in the query.Step 9
For this example, the fields have already been selected for you.Step 10
Now, view the fields of the query on the Fields page.
Click the Fields tab.Step 11
Use the Fields page to view the field properties of the fields you selected.Step 12
In this example, specify that the data will be sorted by ID first, and then by effective date.Step 13
Click the Reorder / Sort button.Step 14
Use the Edit Field Ordering page to modify the order of the columns.Step 15
Click in the New Column field.Step 16
Enter the desired information into the New Column field. Enter
Click the OK button.Step 18
You next need to add criteria to the query.
Click the Criteria tab.Step 19
Use the Criteria page to view existing criteria or to add additional criteria to a query.Step 20
When you choose a record that has EFFDT as a key field, Query Manager automatically creates default criteria. You want to edit this field to define specific dates. When you use effective-date comparisons, you will be returning one effective-dated row of information per item, and you must choose with what you want the effective date compared. The default is to use the effective date that is less than or equal to the current system date. There are other options you can use for comparison.
You use the Edit Criteria Properties page to enter or modify selection criteria for the query.
Click the Edit button.Step 21
Use the Edit Criteria Properties page to enter or modify selection criteria for the query.Step 22
The Condition Type drop-down lists the operators available for the Effective Date field. The value selected for this field determines how PeopleSoft Query will compare values for expressions.
Click the Condition Type list.Step 23
The options include:
• EFF Date <; Returns rows for dates less than the specified date.
• Eff Date <=; Returns rows for dates less than or equal to the specified date.
• EFF Date >; Returns rows for dates greater than the specified date.
• Eff Date >=; Returns rows for dates greater than or equal to the specified date.
• First Eff Date; Returns the row that contains the lowest (oldest) effective-date value.
• Last Eff Date; Returns the row that contains the highest (most recent) effective-date value.
• No effective date; Does not use the effective date logic.
Note that the effective date operators do not always return a single effective-dated row. For example, Eff Date <= returns the one row whose EFFDT value is most recent. Whereas, a not greater than operator returns the currently active row and all history rows.Step 24
If you choose one of the comparison options, choose to compare each row's effective date against today's date or a date other than today.
Select the Current Date option to use today's date.Step 25
Select a Constant 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.Step 26
Select a Field option when you want to see the rows that were effective at the same time as some other record.Step 27
Select an Expression option if you want to prompt users for an effective date when they run the query.Step 28
For this example, you will use the default criteria.
Click the Cancel button.Step 29
In this example, you want to add some additional criteria.
Click the Add Criteria button.Step 30
First, you want to specify campus address types.
Click the Select Record and Field button.Step 31
Click the A.ADDRESS_TYPE - Address Type link.Step 32
Click in the Constant field.Step 33
Enter the desired information into the Constant field. Enter
Click the OK button.Step 35
Click the Add Criteria button.Step 36
Next, specify California addresses.
Click the Select Record and Field button.Step 37
Click the A.STATE - State link.Step 38
Click in the Constant field.Step 39
Enter the desired information into the Constant field. Enter
Click the OK button.Step 41
Click the Save button.Step 42
Use the Enter a name to save this query: page to specify a name and description for the new query you created.Step 43
Enter the desired information into the Query field. Enter
Click in the Description field.Step 45
Enter the desired information into the Description field. Enter
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 47
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 48
Click the OK button.Step 49
Next, view the query results.
Click the Run tab.Step 50
Use the Run page to view the results of your query.Step 51
The query has returned only the current effective-dated rows. Notice that there are 12 rows of data.Step 52
Next, see what happens if you remove the effective-dated criteria.
Click the Criteria tab.Step 53
Click the Delete button.Step 54
Click the Run tab.Step 55
Notice that there are now 14 rows of output, three of which are addresses for one ID, AA0012. In other words, all effective-dated rows appear for ID AA0012.Step 56
You have successfully created an effective-dated query.
Table of Contents Start Topic