enterprise query for hrms 9.0 created on 2/5/2010 … training material... · peoplesoft query...
Post on 06-Feb-2018
216 Views
Preview:
TRANSCRIPT
Enterprise Query for HRMS 9.0 Created on 2/5/2010 12:48:00 PM
COPYRIGHT & TRADEMARKS
Copyright © 1998, 2009, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names
may be trademarks of their respective owners.
This software and related documentation are provided under a license agreement
containing restrictions on use and disclosure and are protected by intellectual property
laws. Except as expressly permitted in your license agreement or allowed by law, you
may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute,
exhibit, perform, publish or display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software, unless required by law for
interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted
to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone
licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS
Programs, software, databases, and related documentation and technical data delivered to
U.S. Government customers are “commercial computer software” or “commercial
technical data” pursuant to the applicable Federal Acquisition Regulation and agency-
specific supplemental regulations. As such, the use, duplication, disclosure, modification,
and adaptation shall be subject to the restrictions and license terms set forth in the
applicable Government contract, and, to the extent applicable by the terms of the
Government contract, the additional rights set forth in FAR 52.227-19, Commercial
Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway,
Redwood City, CA 94065.
This software is developed for general use in a variety of information management
applications. It is not developed or intended for use in any inherently dangerous
applications, including applications which may create a risk of personal injury. If you use
this software in dangerous applications, then you shall be responsible to take all
appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of
this software. Oracle Corporation and its affiliates disclaim any liability for any damages
caused by use of this software in dangerous applications.
This software and documentation may provide access to or information on content,
products and services from third parties. Oracle Corporation and its affiliates are not
responsible for and expressly disclaim all warranties of any kind with respect to third
party content, products and services. Oracle Corporation and its affiliates will not be
responsible for any loss, costs, or damages incurred due to your access to or use of third
party content, products or services.
Training Guide Enterprise Query for HRMS 9.0
Page iii
Table of Contents Enterprise Query for HRMS 9.0 ...................................................................................1
PeopleSoft Query Basics ....................................................................................................... 1 Running Existing Query Using Query Viewer ................................................................................... 2 Running an Existing Query Using Query Manager ............................................................................ 4 Creating a Query .............................................................................................................................. 9 Downloading Queries to Excel and CSV files ................................................................................. 19 Submitting Process Requests .......................................................................................................... 27 Making a Query Distinct ................................................................................................................. 34 Renaming Queries .......................................................................................................................... 44 Editing Queries............................................................................................................................... 48 Deleting Queries ............................................................................................................................. 62
Defining Query Selection Criteria ...................................................................................... 65 Entering Selection Criteria .............................................................................................................. 66 Specifying Effective Date Criteria ................................................................................................... 76 Applying an Aggregate Function .................................................................................................... 85 Creating a Query Using Having Criteria .......................................................................................... 94
Advanced Query Options .................................................................................................. 106 Defining Expressions .................................................................................................................... 107 Creating Record Hierarchy Joins ................................................................................................... 123 Creating a Query Runtime ............................................................................................................ 141 Creating Record Joins ................................................................................................................... 162
Training Guide Enterprise Query for HRMS 9.0
Page 1
Enterprise Query for HRMS 9.0 The main reason you store your business data in a database is so you can manipulate it to answer
questions and solve business problems. However, getting just the information you're looking for often can be a difficult and time-consuming process.
With PeopleSoft Query, you can easily create queries to access the data in your PeopleSoft database. The queries can be as simple or as complex as necessary, and they can be one-time ad-
hoc queries or queries you'll use repeatedly.
Here are some examples of how you can use PeopleSoft Query:
• Preview queries within Query Manager and Query Viewer, displaying the results set in a grid
for review. This option is useful as you refine your queries.
• Run queries from Query Manager or Query Viewer as a separate process, and view the results in a separate browser window.
• Schedule queries so that they run at predefined times or on recurring schedules. The results of
scheduled queries are routed to PeopleSoft Report Manager. • Download and format query results as a Microsoft Excel spreadsheet. This option is available
whether you preview, run, or schedule a query.
• Queries are a primary data source for PeopleSoft Cube Manager, which you can use to build online analytical processing (OLAP) cubes. PeopleSoft nVision also use queries as a data source.
Upon completion of this module, you will be able to:
• Find an existing query. • Create a query.
• Run a query.
• Download a query to Excel and a CVS file. • Submit a process request.
• Rename a query.
• Delete a query.
• Create a query with a selection criteria. • Specify effective date criteria.
• Apply aggregate function to a field.
• Create a query using Having criteria. • Define expressions for a query.
• Create a Record Hierarchy join.
• Create a Related Record join.
PeopleSoft Query Basics
PeopleSoft Query is a reporting tool that enables data to be written directly on screen, enables the creation of one-time ad-hoc queries, and automatically generates SQL code. PeopleSoft Query
enables you to identify and extract precise information using visual representations of your
PeopleSoft database, without writing SQL statements.
Upon completion of this lesson, you will be able to:
• Run an existing query using Query Viewer.
• Run an existing query using Query Manager. • Create a query.
Training Guide
Enterprise Query for HRMS 9.0
Page 2
• Download a query to Excel and a CSV file.
• Submit a process request.
• Make a query distinct. • Rename a query.
• Edit a query.
• Delete a query.
Running Existing Query Using Query Viewer
You can run existing queries and view the results in a new browser window using Query Viewer.
Query Viewer enables you to:
• Search for a query. • Preview a query in the active browser window.
• Run a query and display results in a new browser window.
• Print a query.
• Schedule a query.
In this topic, John Peterson works in your company's Australia office. You know that he is in the
Information Services department, but are unsure if his actual title. You decide to run a query for global employees to verify his job title. Search for and run the
CUSTOM04_GLOBAL_EMPLOYEE_LISTI query to verify John Peterson's job title.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 3
Step Action
1. Begin by navigating to the Query Viewer search page.
Click the Reporting Tools link.
Step Action
2. Click the Query Viewer link.
3. Use the Query Viewer search page to define search criteria for the existing query.
Training Guide
Enterprise Query for HRMS 9.0
Page 4
Step Action
4. Enter the desired information into the begins with field. Enter "CUSTOM04".
5. Click the Search button.
6. Queries that meet the criteria you entered display under Search Results. Notice the
CUSTOM04_GLOBAL_EMPLOYEE_LISTI query is displayed.
From here you can open a query in a new browser window, download a query to an Excel spreadsheet, schedule a query to run, or add a query to your Favorites.
7. Click the HTML link.
8. Use the CUSTOM04_GLOBAL_EMPLOYEE_LISTI page to review the details of your query request. This page opens up in a new window.
9. You can locate the details for John Peterson on this query results page.
10. You used Query Viewer to search for and view a query. Query Viewer is a read-only
version of Query Manager, which enables security administrators to easily limit user
access to queries.
End of Procedure.
Running an Existing Query Using Query Manager You can run predefined queries from your browser and view it online using Query Manager. You
use the Run to HTML option on the Query Manager search page to display the results in a new browser window.
Training Guide Enterprise Query for HRMS 9.0
Page 5
In addition to previewing and running predefined queries, Query Manager enables you to create
and modify queries.
In this topic, you want to review the different departments in your organization.
Procedure
Step Action
1. Begin by navigating to the Query Manager search page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 6
Step Action
2. Click the Query Manager link.
3. Use the Query Manager search page to define search criteria for an existing query. Use a keyword to search for the Department Table query.
Training Guide Enterprise Query for HRMS 9.0
Page 7
Step Action
4. Enter the desired information into the begins with field. Enter "PER".
5. Click the Search button.
6. The search results display all the queries beginning with PER. Notice the
PER701_DEPT_TBL query is displayed.
From here you can edit a query, open the query in a new browser window, download
a query to an Excel spreadsheet, or schedule a query to run.
7. Open the PER701_DEPT_TBL query in a new browser window.
Click the HTML link.
8. The PER701_DEPT_TBL query appears in a new window. The default view will always display a maximum of 100 rows. You can print your results using the
browser print function. View the details and close the browser window.
Training Guide
Enterprise Query for HRMS 9.0
Page 8
Step Action
9. Click the vertical scrollbar.
Training Guide Enterprise Query for HRMS 9.0
Page 9
Step Action
10. Click the Close button.
11. You successfully ran a predefined query by using Query Manager.
End of Procedure.
Creating a Query Creating your own queries enables you to select the table or tables from which you need to
retrieve data. You can also select the fields within the tables so that the query displays only the required data.
This topic provides the basic information of how to select tables and fields for creating queries by using Query Manager. When creating a query, you can specify query attributes and perform such
tasks as modifying column headings and specifying the sort order.
Your manager has asked you for an updated list of employee information. The list should include
employee ID, name, and address information. Your goal is to create a query using the
PERSONNEL_RPT record.
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 10
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Step Action
2. Click the Query Manager link.
3. Use the Query Manager search page to define search criteria for an existing query,
or to create a new query.
Training Guide Enterprise Query for HRMS 9.0
Page 11
Step Action
4. You need to create a query using the PERSONNEL_RPT record.
Click the Create New Query link.
Training Guide
Enterprise Query for HRMS 9.0
Page 12
Step Action
5. Enter the desired information into the begins with field. Enter "PERSONNEL".
6. Click the Search button.
7. Use the Records page to view existing records. Select an existing record to create a
new query.
8. Click the Add Record link.
9. The Query page appears, displaying several fields. Use this page to add fields to a query.
Training Guide Enterprise Query for HRMS 9.0
Page 13
Step Action
10. Add the fields Name, Country, Address1, City, State, and Postal to the query.
Click the NAME - Name option.
11. Click the vertical scrollbar.
12. Click the COUNTRY - Country option.
13. Click the ADDRESS1 - Address Line 1 option.
14. Click the CITY - City option.
15. Click the STATE - State option.
16. Click the POSTAL - Postal code option.
17. Click the vertical scrollbar.
18. Click the Fields tab.
Training Guide
Enterprise Query for HRMS 9.0
Page 14
Step Action
19. The field you selected appears on the Fields page. Query displays a letter next to
each field in the Record.Fieldname column. This letter is an alias that represents
the table from which this field has been extracted
Step Action
20. Click the Edit button.
21. Use the Edit Field Properties page to customize your field properties.
Training Guide Enterprise Query for HRMS 9.0
Page 15
Step Action
22. Click the Text option.
23. Click in the Heading Text field.
24. Enter the desired information into the Heading Text field. Enter "Full Name".
25. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 16
Step Action
26. Click the Edit button.
Training Guide Enterprise Query for HRMS 9.0
Page 17
Step Action
27. Click the Text option.
28. Click in the Heading Text field.
29. Enter the desired information into the Heading Text field. Enter "Street".
30. Click the OK button.
Step Action
31. Click the Save button.
32. Use the Enter a name to save this query: page to specify a name and description
for the new query you created.
Training Guide
Enterprise Query for HRMS 9.0
Page 18
Step Action
33. Enter the desired information into the Query field. Enter "EMP_ADD".
34. Click in the Description field.
35. Enter the desired information into the Description field. Enter "Employee
Address".
36. 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.
37. You have the option of saving the query as either Private or Public. Private means
that only the user ID that created the query can open, run, modify, or delete the query. Public means that any user with access to the records used by the query can
run, modify, or delete the query.
Click the Owner list.
38. Click the Public list item.
39. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 19
Step Action
40. Click the Run tab.
41. Use the Run page to preview the query you have just created.
42. Creating your own queries allows you to select the table or tables from which you
want to execute a query and to design the fields within those tables so that only the
data you want displays.
End of Procedure.
Downloading Queries to Excel and CSV files When you run a query from Query Manager, the results display in a new browser window. From there, you can download Query results to an Excel spreadsheet, or to a CSV text file.
In this topic, you will download the EMP_ADD query results to an Excel spreadsheet, then you
will save the results to your desktop as a CSV file.
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 20
Step Action
1. Begin by navigating to the EMP_ADD page.
Click the Reporting Tools link.
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 21
Step Action
3. Enter the desired information into the begins with field. Enter "EMP_ADD".
4. Click the Search button.
5. Click the HTML link.
6. Use the EMP_ADD page to download query results data in an Excel spreadsheet, or
a CSV text file.
Training Guide
Enterprise Query for HRMS 9.0
Page 22
Step Action
7. Click the Excel Spreadsheet link.
Training Guide Enterprise Query for HRMS 9.0
Page 23
Step Action
8. Click the Open button.
9. The window now displays the results in an Excel spreadsheet.
You can save your file on your local hard drive by modifying the File Type Option
settings for Excel Worksheets.
Step Action
10. Click the Close button.
Training Guide
Enterprise Query for HRMS 9.0
Page 24
Step Action
11. Next, save the results to your desktop as a CSV Text File.
Click the CSV Text File link.
Training Guide Enterprise Query for HRMS 9.0
Page 25
Step Action
12. The File Download window gives you the choice of opening the file, or saving it to disk.
Click the Save button.
13. Click the Save button.
Training Guide
Enterprise Query for HRMS 9.0
Page 26
Step Action
14. You are notified that the download is complete.
Click the Close button.
Training Guide Enterprise Query for HRMS 9.0
Page 27
Step Action
15. Close the browser window to return to PeopleSoft.
Click the Close button.
16. You can download query results from the browser window to an Excel spreadsheet,
or to a CSV text file.
End of Procedure.
Submitting Process Requests You may choose to schedule queries so that they run at predefined times or on recurring
schedules. Query Manager interacts with PeopleSoft Process Scheduler to enable you to schedule queries. To schedule a query, you must submit a process request. The results of scheduled queries
are routed to PeopleSoft Report Manager.
In this topic, you would like to review transfers that occurred as a result of a reorganization. You
want the query to run at 5:00 p.m. today.
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 28
Step Action
1. Begin by navigating to the Schedule Query page.
Click the Reporting Tools link.
Training Guide Enterprise Query for HRMS 9.0
Page 29
Step Action
2. Click the Schedule Query link.
Training Guide
Enterprise Query for HRMS 9.0
Page 30
Step Action
3. Click the Add a New Value tab.
4. Enter the desired information into the Run Control ID field. Enter "TRANSFER".
5. Click the Add button.
6. Use the Schedule Query page to enter the request parameters. These parameters
will be used to define the processing rules and data to be included when the process is run.
Step Action
7. Click in the Query Name field.
8. Enter the desired information into the Query Name field. Enter "HR_TRANS".
9. Click the Search button.
Training Guide Enterprise Query for HRMS 9.0
Page 31
Step Action
10. The description appears to the right of the query name.
Click the HR_TRANSAFTERORGCHG link.
Training Guide
Enterprise Query for HRMS 9.0
Page 32
Step Action
11. Enter the desired information into the Org Plan field. Enter "J02".
12. Click the OK button.
13. The Description field is automatically populated after searching on the query name.
14. Click the Run button.
15. Use the Process Scheduler Request page to enter or update parameters, such as
server name and process output format.
Training Guide Enterprise Query for HRMS 9.0
Page 33
Step Action
16. You must select a Server Name to identify the server on which the process will run. If you use the same Run Control ID for subsequent processes, the server name that
you last used will default in this field.
Click the Server Name list.
17. Click the PSNT list item.
18. You want this process to run at 5:00 p.m. tonight.
Click in the Run Time field.
19. Enter the desired information into the Run Time field. Enter "5:00:00PM".
20. Use the Type field to select the type of output you want to generate for this job.
21. Use the Format field to define the output format for the report. The values are
dependent upon the Process Type you have selected. In this example, the default value is TXT.
22. Click the OK button.
23. Notice the Process Instance number appears. This number helps you identify the
process you have run when you check the status.
Training Guide
Enterprise Query for HRMS 9.0
Page 34
Step Action
24. You have scheduled the query to run at a future time. You can view results from the
Process List page.
Click the Process Monitor link.
25. Use the Process List page to view the status of submitted process requests.
26. The current status of the process is Queued. The process is finished when the status is Success. You can click the Refresh button to update the status, which will change
to Success after the query runs at 5:00PM today.
27. You have successfully scheduled a query to run at a later date and/or time.
End of Procedure.
Making a Query Distinct You can use the Distinct feature of Query to avoid duplicity of fields in a report.
In this topic, you want to retrieve a list of employees who have requested vacation. You want each employee ID to appear only once. To do this, you will create a query based on the
ABSV_REQUEST record and make the query distinct.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 35
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 36
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 37
Step Action
3. Click the Create New Query link.
Step Action
4. In this example, you want to create a query by using the ABSV_REQUEST record.
Enter the desired information into the begins with field. Enter
"ABSV_REQUEST".
5. Click the Search button.
6. Use the Records page to select the records upon which to base the new query.
7. Add the record and select the fields from the record that you want to add to the
query.
Click the Add Record link.
8. The Query page appears, displaying several fields. Use this page to add fields to a query.
9. The Query Name appears as New Unsaved Query until you save the query.
Training Guide
Enterprise Query for HRMS 9.0
Page 38
Step Action
10. Click the EMPLID- EmplID option.
11. Click the Save button.
12. Use the Enter a name to save this query: page to specify a name and description
for the new query you create.
Training Guide Enterprise Query for HRMS 9.0
Page 39
Step Action
13. Enter the desired information into the Query field. Enter "Employee_Distinct".
14. Click in the Description field.
15. Enter the desired information into the Description field. Enter "List of employee
vacation reqs".
16. 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 this example, retain the default query type.
17. You can specify the query as either Private or Public by selecting an entry in the
Owner field. A Private query can be accessed and modified by only the user who
created the query. However, any user who has access to the query records can run, modify, or delete a Public query. For this example, retain the default value.
18. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 40
Step Action
19. Next, view the query results.
Click the Run tab.
20. Use the Run page to preview the query you have just created. Notice that there are 5
rows in the query result, and some IDs are appearing multiple times.
Training Guide Enterprise Query for HRMS 9.0
Page 41
Step Action
21. Click the Fields tab.
22. Use the Fields page to view how fields are selected for output; view the properties
of each field; and to change headings, order-by numbers, and aggregate values.
Training Guide
Enterprise Query for HRMS 9.0
Page 42
Step Action
23. Navigate to the Query Properties page to change the query properties to make it
distinct.
Click the Properties link.
24. Use the Query Properties page to enter query details such as query description,
type, and owner.
Training Guide Enterprise Query for HRMS 9.0
Page 43
Step Action
25. Click the Distinct option.
26. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 44
Step Action
27. Click the Save button.
28. Now, preview the query results.
Click the Run tab.
29. Notice that the page now has only 3 rows. The IDs that were appearing more than once have disappeared from the query results.
30. You have successfully made a query distinct.
End of Procedure.
Renaming Queries There may be instances when you need to rename an existing query. If your security access
allows, you can change the name of a query from the query search results page.
In this topic, you have been asked to change the name of the EMP_ADD query to EMPLOYEE_ADDRESS.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 45
Step Action
1. Begin by navigating to the Rename Queries page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 46
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 47
Step Action
3. Use a keyword to search for the EMP_ADD query.
Enter the desired information into the begins with field. Enter "EMP_ADD".
4. Click the Search button.
5. You can rename a query from the search results page. Select the query to be
renamed.
Click the EMP_ADD option.
6. Use the Action field to select an option to perform the required operation, such as
copy, delete, move, or rename, on the selected query. For this exercise you will
rename the selected query.
Click the Action list.
7. Click the Rename Selected list item.
8. Click the Go button.
9. Use the Rename Queries page to specify the new name for the query.
Training Guide
Enterprise Query for HRMS 9.0
Page 48
Step Action
10. Enter the desired information into the New Name field. Enter
"EMPLOYEE_ADDRESSES".
11. Click the OK button.
12. Notice that the query name has changed to EMPLOYEE_ADDRESSES.
13. You successfully renamed a query.
End of Procedure.
Editing Queries The Edit Field Properties page appears when you click the Edit button for a field on the Fields page. You can access the Edit Field Properties page from any page except the Records and
Preview pages. The fields that appear on the Edit Field Properties page depend on the type of
field value you want to edit.
In this topic, a query has already been created and you are going to edit the column order, sort
order, and column heading titles for the query.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 49
Step Action
1. Begin by navigating to the Fields page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 50
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 51
Step Action
3. Enter the desired information into the begins with field. Enter
"EMPLOYEE_ADDRESSES".
4. Click the Search button.
5. Click the Edit link.
6. Use the Fields to view how fields are selected for output, view the properties of each field, change headings, change column and sort orders, and apply aggregate values.
Step Action
7. Click the Query tab.
8. Use the Query page to select the fields you want to add to your query, or to deselect
fields to remove form your query.
Training Guide
Enterprise Query for HRMS 9.0
Page 52
Step Action
9. Expand the folder to show the fields.
Click the Expand button.
10. In this example, the query record and fields have already been selected. Begin by
viewing the results of the query.
11. Next, view the results of your query.
Click the Run tab.
12. Use the Run page to view the results of your query.
13. After viewing the results, you decide to change the order of the columns.
Training Guide Enterprise Query for HRMS 9.0
Page 53
Step Action
14. Click the Fields tab.
15. The columns in the query results display in the order specified in the Col column on
the Fields page.
Training Guide
Enterprise Query for HRMS 9.0
Page 54
Step Action
16. Click the Reorder / Sort button.
17. Use the Edit Field Ordering page to modify the order of the columns.
Training Guide Enterprise Query for HRMS 9.0
Page 55
Step Action
18. In this example, you want the columns in the following order: Name, City, Address1, State, Postal Code, Country.
Click in the New Column field.
19. Enter the desired information into the New Column field. Enter "6".
20. For this example, the ADDRESS1 column order is not changing.
21. Click in the New Column field.
22. Enter the desired information into the New Column field. Enter "2".
23. Click in the New Column field.
24. Enter the desired information into the New Column field. Enter "4".
25. Click in the New Column field.
26. Enter the desired information into the New Column field. Enter "5".
27. Click the OK button.
28. Note that the fields are now listed with the updated column order.
Training Guide
Enterprise Query for HRMS 9.0
Page 56
Step Action
29. View the results of the query with the modified column order.
Click the Run tab.
30. The column order is now changed in the query results.
Training Guide Enterprise Query for HRMS 9.0
Page 57
Step Action
31. Click the Fields tab.
32. The column headings for each column can also be modified. You next want to
change the titles displayed on two of the columns.
33. The column heading used in the query results is displayed in the Heading Text
column.
The text that is listed here is used for the headings in your output to grid control,
Excel, and Crystal (if you export from Windows). If, however, you create a Crystal report from within Crystal Reports, the field name is used in the column heading.
Training Guide
Enterprise Query for HRMS 9.0
Page 58
Step Action
34. In this example, you want the Name column heading changed to Employee.
Click the Edit button.
35. Use the Edit Field Properties page to enter edits, updates, or changes to field
properties.
36. You have the choice of using no heading, the short description, the long description,
or user-defined text for the column heading.
Training Guide Enterprise Query for HRMS 9.0
Page 59
Step Action
37. Click in the Heading Text field.
38. Enter the desired information into the Heading Text field. Enter "Employee".
39. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 60
Step Action
40. In this example, you also want to change the POSTAL - Postal Code heading text to
Zip Code.
Click the Edit button.
Training Guide Enterprise Query for HRMS 9.0
Page 61
Step Action
41. The short description is used by default. You want to change this to use a text description.
Click the Text option.
42. Click in the Heading Text field.
43. Enter the desired information into the Heading Text field. Enter "Zip Code".
44. Click the OK button.
45. Notice that the new text descriptions are now displayed in the Heading Text field.
Training Guide
Enterprise Query for HRMS 9.0
Page 62
Step Action
46. Review the results of the query with the new column headings.
Click the Run tab.
47. Notice the updated column headings.
48. You have successfully edited a query by changing the column order, sort order, and
column headings.
End of Procedure.
Deleting Queries You can delete outdated and obsolete queries, if necessary, to better organize you company's
database. Note that the ability to delete or rename a query is dependent upon user roles and user security.
In this topic, you will delete an existing query. The CUSTOM00_GENDER_RATIO_ANALYSIS query has become outdated and is now obsolete.
Your goal is to delete it. Use the Query Manager page to find and delete the
CUSTOM00_GENDER_RATIO_ANALYSIS query.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 63
Step Action
1. Begin by navigating to the Query Manager page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 64
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 65
Step Action
3. Enter the desired information into the begins with field. Enter
"CUSTOM00_GENDER_RATIO_ANALYSIS".
4. Click the Search button.
5. Use the Query Manager page to update query information such as deleting,
renaming, or copying.
6. Click the CUSTOM00_GENDER_RATIO_ANALYSIS option.
7. Click the Action list.
8. You can select an option to perform the required operation, such as copy, delete,
move, or rename, on the selected query. In this example, you need to delete the selected query.
Click the Delete Selected list item.
9. Click the Go button.
10. The Delete Confirmation page enables you to confirm that you want to delete the
query you have identified.
Click the Yes button.
11. Notice that the CUSTOM00_GENDER_RATIO_ANALYSIS query has been
deleted.
12. You successfully deleted the a query.
End of Procedure.
Defining Query Selection Criteria
PeopleSoft database stores data in tables made up of columns (fields) and rows (records). This
helps you to identify every individual piece of data that you need to specify. When you create a query, you select the data you want the system to retrieve by specifying from which columns and
rows to retrieve.
When you run a query after selecting the fields, the system retrieves the data from every row in the table or tables. If you want the system to retrieve data from selective rows, you add selection
criteria to the query.
The selection criteria serves as a test that the system applies to each row of data in the tables
related to your query. If the row passes the test, the application retrieves it. If the row does not
pass the test, however, the application does not retrieve the data.
Training Guide
Enterprise Query for HRMS 9.0
Page 66
For example, you need the names of all PeopleSoft customers who are not PeopleSoft employees.
You start by creating a query that retrieves the Name and Company fields from the Customer table. You can then add a selection criterion that tells PeopleSoft Query to scan for rows in which
the company name is not PeopleSoft. In most cases, a selection criterion compares the value in
one of a row's fields to a reference value. In this example, you would compare the value in the Company field to the constant value of PeopleSoft.
Upon completion of this lesson, you will be able to:
• Enter selection criteria. • Specify effective date criteria.
• Apply an aggregate function.
• Create a query using Having criteria.
Entering Selection Criteria
Defining selection criteria enables you to selectively retrieve data. Selection criteria refines your query by specifying conditions that the retrieved data must meet. You can define selection criteria
for a new or an existing query.
In this topic, your manager would like a list of employees that live in California. To generate this
list, you will use the EMPLOYEE_ADDRESS query and define the criteria properties to include
only employees who live in California.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 67
Step Action
1. Begin by navigating to the Fields page.
Click the Reporting Tools link.
Step Action
2. Click the Query Manager link.
Training Guide
Enterprise Query for HRMS 9.0
Page 68
Step Action
3. Enter the desired information into the begins with field. Enter
"EMPLOYEE_ADDRESSES".
4. Click the Search button.
5. Click the Edit link.
6. Use the Fields page to view how fields are selected for output; view the properties of each field; and to change headings, order-by numbers, and aggregate values.
Training Guide Enterprise Query for HRMS 9.0
Page 69
Step Action
7. The EMPLOYEE_ADDRESSES query is displayed. Before adding criteria to the query, run the query result to see how many rows are returned.
Click the Run tab.
8. Use the Run page to view the results of your query.
9. The query result displays all 2293 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.
Training Guide
Enterprise Query for HRMS 9.0
Page 70
Step Action
10. Click the Criteria tab.
11. 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.
Training Guide Enterprise Query for HRMS 9.0
Page 71
Step Action
12. Click the Add Criteria button.
13. Use the Edit Criteria Properties page to define the selection criteria for the query.
14. First, you need to select the expression to be used as a comparison value. 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.
15. Use the Expression 1 group box, to specify the field you want to use as criteria.
In this example, you need to retrieve information about a Campus; therefore, locate the A.STATE - State field.
Training Guide
Enterprise Query for HRMS 9.0
Page 72
Step Action
16. Click the Select Record and Field button.
Training Guide Enterprise Query for HRMS 9.0
Page 73
Step Action
17. Click the vertical scrollbar.
18. Click the A.STATE - State link.
19. 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.
20. In this example, you want to display a State that is equal to California. Therefore,
you will leave the condition type as equal to.
21. 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.
22. 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 select 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.
23. 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 select 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.
Training Guide
Enterprise Query for HRMS 9.0
Page 74
Step Action
24. 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.
25. 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.
26. 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.
27. In this example, you are going to select a specific value, so you will use the default
Constant option.
Training Guide Enterprise Query for HRMS 9.0
Page 75
Step Action
28. Next, specify the campus value for which you are looking.
Click in the Constant field.
29. Enter the desired information into the Constant field. Enter "CA".
30. Click the OK button.
Step Action
31. Notice the first criteria is displayed.
Click the Save button.
32. Finally, view the results of the query.
Click the Run tab.
33. The results display all employees who live in California.
34. You successfully created a query with criteria properties.
End of Procedure.
Training Guide
Enterprise Query for HRMS 9.0
Page 76
Specifying Effective Date Criteria Effective-dated tables have record definitions that include the Effective Date (EFFDT) field. This field, used throughout PeopleSoft applications, provides a historical perspective, allowing you to
see how the data has changed over time.
When you are using a PeopleSoft application for day-to-day processing, you usually want the system to give you the currently effective rows of data. Essentially, the system must return the
row in which the effective date is less than or equal to the current date. You do not need to see the
history rows, which are no longer accurate, nor do you need to see future-dated rows, which are not yet in effect.
When you are querying an effective-dated table, you may want to view some rows that are not currently in effect or, you may want to view all the rows, regardless of their effective dates.
Additionally, you may want to view only the rows that were effective as of a specific date.
In this topic, you have been asked to run the ADDL_PAY_EFFDT query to determine which employees have earned additional pay. Your manager has requested that the query be run for
specific effective dates. Your goal is to run the first query to include only employees who have
received additional pay a specified date. The second query is for all other dates.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 77
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Step Action
2. Click the Query Manager link.
Training Guide
Enterprise Query for HRMS 9.0
Page 78
Step Action
3. Click the Create New Query link.
Training Guide Enterprise Query for HRMS 9.0
Page 79
Step Action
4. Enter the desired information into the begins with field. Enter "ADDL_PAY_EFFDT".
5. Click the Search button.
6. Use the Records page to view existing records. Select an existing record to create a new query.
7. Click an entry in the Add Record column.
Step Action
8. A dialog box appears that indicates that the effective date criteria has been
automatically added to this effective dated record.
Click the OK button.
9. 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.
Training Guide
Enterprise Query for HRMS 9.0
Page 80
Step Action
10. In this example, you want to use the following fields: EMPLID, ERNCD, and
EFFDT.
Click the EMPLID - EmplID option.
11. Click the ERNCD - Earnings Code option.
12. Click the EFFDT - Effective Date option.
13. Next, add criteria to the query.
Click the Criteria tab.
14. Use the Criteria page to view existing criteria or to add additional criteria to a
query.
Training Guide Enterprise Query for HRMS 9.0
Page 81
Step Action
15. 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.
Click the Edit button.
16. Use the Edit Criteria Properties page to enter or modify selection criteria for the
query.
17. The Condition Type drop-down lists the operators that are available for the Effective Date field. The value selected for this field determines how PeopleSoft
Query is going to compare values for the different expressions.
18. Keep in mind that the effective date operators work differently than the standard
comparison operators. These operators 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.
Training Guide
Enterprise Query for HRMS 9.0
Page 82
Step Action
19. In this example, you have been asked to see information on employees who have
received additional pay between a set period of time.
Click the Condition Type list.
20. Click the between list item.
21. 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 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.
22. Select a Field 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'll want to see the products that were effective on the date of the
order.
23. Select an Expression option if you want to prompt users for an effective date when they run the query.
For this example, you will use the default Constant-Constant option.
24. Click in the Date field.
25. Enter the desired information into the Date field. Enter "01/01/2000".
Training Guide Enterprise Query for HRMS 9.0
Page 83
Step Action
26. Click in the Date 2 field.
27. Enter the desired information into the Date 2 field. Enter "01/31/2000".
28. Click the OK button.
Step Action
29. Next, view the query results.
Click the Run tab.
30. Use the Run page to view the results of your query.
31. A list of employees who received additional pay between January 1 and January 31,
2000 is displayed.
You have created a query that satisfies the effective date criteria. Now, redefine this
query so that it includes employees who have received additional pay for all other dates.
Training Guide
Enterprise Query for HRMS 9.0
Page 84
Step Action
32. Click the Criteria tab.
Training Guide Enterprise Query for HRMS 9.0
Page 85
Step Action
33. You can use logical operators to include all dates except the ones specified. To do
this, you use the NOT operator.
Click the Logical list.
34. Click the NOT list item.
35. Preview the query with the new criteria applied.
Click the Run tab.
36. Now, the query displays the information for all employees who received additional pay for dates other than January 1 through January 31, 2000.
37. In this topic, you learned to create an effective-dated query. This query is used to
retrieve data for a specific period from an effective-dated table.
End of Procedure.
Applying an Aggregate Function An aggregate function is a special type of operator that returns a single value based on multiple
rows of data. When your query includes one or more aggregate functions, PeopleSoft Query
collects related rows and displays a single row that summarizes their contents.
For example, suppose you have an Order record that includes Customer ID and Amount fields
for each item ordered. You want to find out how much each customer has ordered. Therefore, you create a query that selects the Customer ID and Amount fields. Without any aggregate
functions, this query would return one row for every customer and amount combination. If you
apply the aggregate function Sum to the Amount field, the query can be narrowed down to display one row that summarizes the amount for each customer.
When you apply an aggregate function to a field, you are redefining how PeopleSoft Query uses
the field throughout the query. Essentially, the application replaces the field, wherever it occurs, with the results of the function. If you select the field as a display column, PeopleSoft Query
displays the aggregate values. However, if you use the field as an order by column, the
application organizes the results in an order that is based on the aggregate values.
In this topic, a few employees at your company have decided to use some vacation time within
the next few months. Your manager needs a list of the employees who have requested vacation
time and the total number of vacation days requested for each employee. Instead of running a query with many rows of data, you decide to create a query for the total number of requested days
off using the aggregate Sum function.
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 86
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Training Guide Enterprise Query for HRMS 9.0
Page 87
Step Action
2. Click the Query Manager link.
Training Guide
Enterprise Query for HRMS 9.0
Page 88
Step Action
3. Click the Create New Query link.
Step Action
4. The first step in creating a new query is to find an existing record for the query. In
this example, you will locate the ABSV_REQUEST record.
Enter the desired information into the begins with field. Enter
"ABSV_REQUEST".
5. Click the Search button.
6. Use the Records page to view existing records. Select an existing record to create a
new query.
7. Click an entry in the Add Record column.
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.
Training Guide Enterprise Query for HRMS 9.0
Page 89
Step Action
9. In this example, you want to use the following fields: EMPLID, BEGIN_DT, RETURN_DT, and DURATION_DAYS.
Click the EMPLID - EmplID option.
10. Click the BEGIN_DT - Begin Date option.
11. Click the RETURN_DT - Return Date option.
12. Click the DURATION_DAYS - Duration (Days) option.
13. Click the Fields tab.
14. Use the Fields page to view how fields are selected for output; view the properties of each field; and to change headings, order-by numbers, and aggregate values.
Training Guide
Enterprise Query for HRMS 9.0
Page 90
Step Action
15. You are going to add an aggregate function to the DURATION_DAYS field.
Click the Edit button.
16. Use the Edit Field Properties page to change the column heading and apply the
aggregate function to this query.
17. The column heading for a field can be specified in the Heading group box.
If No Heading is selected, the column does not have a heading.
If RFT Short is selected, the column heading is the short name from the record
definition. If Text is selected, the column heading is the text you have entered in the text box.
If RFT Long is selected, the column heading is the long name from the record
definition.
Training Guide Enterprise Query for HRMS 9.0
Page 91
Step Action
18. In this example, you want to override the column heading with custom text to reflect that the data is a summation of days.
Click the Text option.
19. Click in the Heading Text field.
20. Enter the desired information into the Heading Text field. Enter "SUM DAYS".
21. An aggregate function is a special type of operator that returns a single value based on multiple rows of data. When your query includes one or more aggregate
functions, Query Manager collects related rows and displays a single row that
summarizes their contents.
22. The options are as follows: None - Will not use aggregate functions.
Sum - Adds the values from each row and displays the total.
Count - Counts the number of rows.
Min - Checks the value from each row and returns the lowest one. Max - Checks the value from each row and returns the highest one.
Average - Adds the values from each row and divides the result by the number of
rows.
Training Guide
Enterprise Query for HRMS 9.0
Page 92
Step Action
23. In this example, specify an aggregate function to calculate the total number of
requested days off.
Click the Sum option.
24. Click the OK button.
Step Action
25. Click the Save button.
26. Use the Enter a name to save this query: page to name and describe your query.
Training Guide Enterprise Query for HRMS 9.0
Page 93
Step Action
27. Enter the desired information into the Query field. Enter "VAC_TIME_OFF".
28. Click in the Description field.
29. Enter the desired information into the Description field. Enter "# of requested
vacation days".
30. Standard queries are designated as User queries. Workflow queries are either
Process or Role queries.
For this example, use the default.
31. 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.
32. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 94
Step Action
33. Finally, you can view the query results on the Run page.
Click the Run tab.
34. The query results display the total number of vacation days for an employee.
35. You successfully applied an aggregate function to a field.
End of Procedure.
Creating a Query Using Having Criteria With SQL (Structured Query Language), a Having clause is like a Where clause for rows of data
that have been aggregated into a single row of output. The system evaluates Where clauses by
looking at the individual table rows before they are grouped by the aggregate function, and then it evaluates Having clauses after applying the function. So, if you want to check the value returned
by the function, you must define Having criteria.
For example, suppose you need a list of the departments in which the minimum salary is greater
than $100,000. In this case, you first use the aggregate function to group the departments based
on the salary. Then, you use the Having clause to obtain the list of the departments in which the
minimum salary is greater than $100,000.
In this topic, your manager has asked you to selectively define the query results of employees
taking vacation time. She would like the query results to include the employees who are taking more than three days off. To accomplish this task, you will apply Having criteria.
Training Guide Enterprise Query for HRMS 9.0
Page 95
Procedure
Step Action
1. Begin by navigating to the Fields page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 96
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 97
Step Action
3. In this example, you are going to begin with an existing query and modify it.
Enter the desired information into the begins with field. Enter "VAC_TIME_OFF".
4. Click the Search button.
5. Click the Edit link.
6. Use the Fields page to view how fields are selected for output; view the properties
of each field; and change headings, order-by numbers, and aggregate values.
Step Action
7. You need to add two additional fields to this query. You add fields on the Query
page.
Click the Query tab.
8. Use the Query page to select the fields you want to add to your query, or to deselect fields to remove form your query.
Training Guide
Enterprise Query for HRMS 9.0
Page 98
Step Action
9. Click the Expand button.
10. You will add the following fields to this query: PLAN_TYPE and ABSENCE_TYPE.
Click the PLAN_TYPE - Plan Type option.
11. Click the ABSENCE_TYPE - Absence Type option.
12. Return to the Fields page to edit the field properties for the PLAN_TYPE field.
Click the Fields tab.
Training Guide Enterprise Query for HRMS 9.0
Page 99
Step Action
13. Click the Edit button.
14. Use the Edit Field Properties page to change the column heading and modify the
translate value displayed in the query results.
Training Guide
Enterprise Query for HRMS 9.0
Page 100
Step Action
15. Query Manager populates the assigned heading to be displayed at the top of the
column for the query output for each field listed. To assign a new heading for the
PLAN_TYPE field, select the Text option and edit the Heading Text field.
Click the Text option.
16. Click in the Heading Text field.
17. Enter the desired information into the Heading Text field. Enter "Plan_Type".
18. The data displayed for the PLAN_TYPE field comes from the Translate Table.
When a value comes from the Translate Table, you can determine how you want the information displayed: None, Short Description, or Long Description. For this
example, you want to use the Short Description.
Click the Short option.
19. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 101
Step Action
20. The next step is to define the Having criteria for the query.
Click the Having tab.
21. Use the Having page to view any existing Having criteria for the query and, if necessary, to add or modify the Having criteria for the query.
Training Guide
Enterprise Query for HRMS 9.0
Page 102
Step Action
22. For this example, you need to add the criteria.
Click the Add Having Criteria button.
23. Use the Edit Having Criteria Properties page to edit or define new having criteria
for the query.
Training Guide Enterprise Query for HRMS 9.0
Page 103
Step Action
24. In this exercise, your manager has requested that query results include vacation days for employees who are on vacation for more than three days. Navigate to the field
that records employee's vacation duration.
Click the Select Record and Field button.
Training Guide
Enterprise Query for HRMS 9.0
Page 104
Step Action
25. Click the A.DURATION_DAYS - Duration (Days) link.
Training Guide Enterprise Query for HRMS 9.0
Page 105
Step Action
26. The Condition Type determines how PeopleSoft Query compares the values of the
first expression to the second expression.
In this example, you want to display data for employees with greater than three days
vacation time.
Click the Condition Type list.
27. Click the greater than list item.
28. Next, define a Constant value.
Click in the Constant field.
29. The value of the DURATION_DAYS field will be compared to the single fixed
value defined here.
In this example, the constant value is 3 because you are looking for information on
employees who are on vacation for more than three days.
Enter the desired information into the Constant field. Enter "3".
30. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 106
Step Action
31. Click the Save button.
32. Next, view the results of the query on the Run page.
Click the Run tab.
33. Use the Run page to view the results of your query.
34. The query results display information for employees with more than three days of vacation.
35. In this topic, you learned to use Having criteria to further refine query results.
End of Procedure.
Advanced Query Options Expressions are calculations that PeopleSoft Query performs as part of a query. You use them
when you must calculate a value that PeopleSoft Query doesn't provide by default. For example,
to add the values from two fields together or to multiply a field value by a constant.
Query Manager also enables you to create queries that include multiple-table joins. Joins retrieve
data from more than one table, presenting the data as if it came from one table. A PeopleSoft
query links the tables, based on common columns, and links the rows in two tables by common values in the shared columns.
Upon completion of this lesson, you will be able to: • Define expressions for a query.
Training Guide Enterprise Query for HRMS 9.0
Page 107
• Create a Record Hierarchy join. • Create a Query at Runtime.
• Create a Related Record join.
Defining Expressions Expressions are calculations that PeopleSoft Query performs as part of a query. Use them when
you must calculate a value that PeopleSoft Query doesn't provide by default (for example, to add the values from two fields together or to multiply a field value by a constant).
An expression can be treated as a field query. When selected for output, you can change its
column heading or sort it. In this topic, you are going to create a query that displays customer information and calculates the total cost of training units for each customer.
You will create an expression to determine what each customer has spent on training units. The expression multiplies the cost of a training unit by the number of units that each customer owns.
In this topic, you are going to create a query that displays the other pay amount to be distributed
to employees on a quarterly basis. The other pay data is an annual amount to be paid to the employee, but your company will process the amount quarterly. You want to divide the total
amount for each employee by four (the number of quarters in a year) to view the distribution
amount.
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 108
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 109
Step Action
3. Click the Create New Query link.
Training Guide
Enterprise Query for HRMS 9.0
Page 110
Step Action
4. 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 ADDL_PAY_DATA record.
Enter the desired information into the begins with field. Enter
"ADDL_PAY_DATA".
5. Click the Search button.
6. Use the Records page to view existing queries or to create a new query.
7. Click the Add Record link.
Step Action
8. When a record is contains an effective date, the appropriate criteria will be added to
your query.
Click the OK button.
9. Use the Query page to select the fields you want to add to your query, or to deselect fields to remove form your query.
Training Guide Enterprise Query for HRMS 9.0
Page 111
Step Action
10. Next, select the fields for the query.
Click the EMPLID - EmplID option.
11. Click the EFFDT - Effective date option.
12. Click the OTH_PAY - Other Pay option.
13. Click the ADDLPAY_REASON - Reason for ... option.
14. Click the Fields tab.
15. Use the Fields page to view how fields are selected for output, view the properties
of each field, change headings, change column and sort orders, and apply aggregate values
Training Guide
Enterprise Query for HRMS 9.0
Page 112
Step Action
16. In this example, you will update the headings for the EmplID and Other Pay fields.
EmplID should be changed to Employee ID, while Other Pay will use the RFT Long
value.
Click the Edit button.
17. Use the Edit Field Properties page to change the column heading and apply the aggregate function to this query.
Training Guide Enterprise Query for HRMS 9.0
Page 113
Step Action
18. Click the Text option.
19. Click in the Heading Text field.
20. Enter the desired information into the Heading Text field. Enter "Employee ID".
21. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 114
Step Action
22. Click the Edit button.
Training Guide Enterprise Query for HRMS 9.0
Page 115
Step Action
23. Click the RFT Long option.
24. Click the OK button.
Step Action
25. Next, you need to add the expression to divide the net amount by four to get a
quarterly amount result.
Click the Expressions tab.
26. Use the Expressions page to add or maintain expression criteria. Create expression
statements by selecting operators, fields, and constant values.
Training Guide
Enterprise Query for HRMS 9.0
Page 116
Step Action
27. Click the Add Expression button.
28. Use the Edit Expression Properties page to select the expression type, and to enter
expression text.
Training Guide Enterprise Query for HRMS 9.0
Page 117
Step Action
29. Select an Expression Type from the drop-down list.
If you select Character, enter the maximum length of the expression result in the
Length field. If you select Number or Signed Number, enter the total number of
digits in the Length field and the number of digits after the decimal point in the Decimal field.
In this example, you are calculating a number.
Click the Expression Type list.
30. Click the Number list item.
31. Click in the Length field.
32. In the Length field, ensure that you set the integer to a large enough number so that
it will not truncate the number if you have not reserved enough places. For example; if you assign Length=2 and your result is 125, it will only display 12 because you
have only reserved two places.
Enter the desired information into the Length field. Enter "8".
33. Select the Aggregate Function check box to create an aggregate function, such as Sum, Avg, or Count.
Training Guide
Enterprise Query for HRMS 9.0
Page 118
Step Action
34. Click in the Decimals field.
35. If you entered Number or Signed Number as the expression type, enter the number
of digits to the right of the decimal.
Enter the desired information into the Decimals field. Enter "2".
36. Click in the Expression Text field.
37. If you know the field name, you can enter it. Precede the field name with an alias
(A, B, and so on). If you mistype the field, you will receive an error message. Alternatively, you can click the Add Field link to add a field to this expression.
Add the additional expression text to the field name. For example *2 to multiply by two, and /3 to divide by three.
Enter the desired information into the Expression Text field. Enter
"A.OTH_PAY/4".
38. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 119
Step Action
39. Use the Use as Field link to display the result of the calculation in the query's output.
Click an entry in the Use as Field column.
Step Action
40. An expression can be treated just as if it were a field in the query: select it for
output, change its column heading, or choose it as an “order by” column.
In this example, you will change the heading text of the expression field.
Click the Edit button.
Training Guide
Enterprise Query for HRMS 9.0
Page 120
Step Action
41. Click in the Heading Text field.
42. Enter the desired information into the Heading Text field. Enter "Quarterly Other
Pay".
43. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 121
Step Action
44. Click the Save button.
45. Use the Enter a name to save this query: page to name and describe your query.
Training Guide
Enterprise Query for HRMS 9.0
Page 122
Step Action
46. Enter the desired information into the Query field. Enter "Expression".
47. Click in the Description field.
48. Enter the desired information into the Description field. Enter "Expression
Example".
49. Standard queries are designated as User queries. Workflow queries are either
Process or Role queries. For this example, use the default.
50. 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.
51. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 123
Step Action
52. Finally, view the results of the query.
Click the Run tab.
53. Use the Run page to view the results of your query.
54. The results display the net and quarterly Other Pay amounts for each employee.
55. You have successfully defined an expression for a query.
End of Procedure.
Creating Record Hierarchy Joins
A record hierarchy joins a Parent table to a Child table. A Child table is a table that uses all the same key fields as its parent, plus one or more additional keys.
In this topic, your company is interested in hiring a full time employee. It wants to interview all
interested temporary employees before opening the position up to the public. You need to create a query to find all temporary employees. The query should include employee ID, hire date,
employee status, regular/temporary, and full/part time information. To create this query, you need
to join two records, PERSON - PERSON and .
Procedure
Training Guide
Enterprise Query for HRMS 9.0
Page 124
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Training Guide Enterprise Query for HRMS 9.0
Page 125
Step Action
2. Click the Query Manager link.
Training Guide
Enterprise Query for HRMS 9.0
Page 126
Step Action
3. Click the Create New Query link.
Step Action
4. Find the first record to be used in your query, PERSON - PERSON.
Enter the desired information into the begins with field. Enter "PERSON".
5. Click the Search button.
6. Use the Records page to view existing records or to add new records.
7. Click the Add Record link.
8. Use the Query page to select fields to add to the query content or add additional
records.
Training Guide Enterprise Query for HRMS 9.0
Page 127
Step Action
9. Click the EMPLID - EmplID option.
10. The next step is to join PRIMARY_JOB_VW (child record) to PERSON -
PERSON (parent record).
Click the Hierarchy Join link.
Training Guide
Enterprise Query for HRMS 9.0
Page 128
Step Action
11. Click the vertical scrollbar.
12. Click the PRIMARY_JOB_VW - Primary Job View - HR link.
Training Guide Enterprise Query for HRMS 9.0
Page 129
Step Action
13. A Windows dialog box appears, indicating that an effective date criteria has been automatically added.
Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 130
Step Action
14. Click the EMPL_STATUS - Payroll Status option.
15. Click the REG_TEMP - Regular/Temporary option.
16. Click the FULL_PART_TIME - Full/Part Time option.
17. Click the Fields tab.
18. Use the Fields page, to edit, delete, or add criteria to each field.
Training Guide Enterprise Query for HRMS 9.0
Page 131
Step Action
19. Edit the properties of the A.EMPLID - EmplID field.
Click the Edit button.
20. Use the Edit Field Properties page to enter edits, updates, or changes to field properties.
Training Guide
Enterprise Query for HRMS 9.0
Page 132
Step Action
21. Click the Text option.
22. Click in the Heading Text field.
23. Enter the desired information into the Heading Text field. Enter "Employee ID".
24. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 133
Step Action
25. Click the Save button.
26. Use the Enter a name to save this query: page to name and describe your query.
Training Guide
Enterprise Query for HRMS 9.0
Page 134
Step Action
27. Enter the desired information into the Query field. Enter "TEMP_EMP".
28. Click in the Description field.
29. Enter the desired information into the Description field. Enter "Temporary
Employees".
30. Use the Query Type field to choose from User Query Type, Process Query Type,
or Role Query Type. Standard queries are defined as User types, and queries that use
workflow are defined as Process or Role types.
31. 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.
Click the Owner list.
32. Click the Public list item.
33. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 135
Step Action
34. Click the Criteria tab.
35. Use the Criteria page to add, edit, and delete criteria. Criteria refines your query by
specifying conditions that the retrieved data must meet.
Training Guide
Enterprise Query for HRMS 9.0
Page 136
Step Action
36. Click the Add Criteria button.
37. Use the Edit Criteria Properties page to define the selection criteria for the query.
Training Guide Enterprise Query for HRMS 9.0
Page 137
Step Action
38. You want to edit the criteria properties for the REG_TEMP - Regular/Temporary field and select Temporary as a constant.
Click the Select Record and Field button.
Training Guide
Enterprise Query for HRMS 9.0
Page 138
Step Action
39. Click the Show Fields button.
40. Click the B.REG_TEMP - Regular/Temporary link.
Training Guide Enterprise Query for HRMS 9.0
Page 139
Step Action
41. Click the Select Constant From List button.
Training Guide
Enterprise Query for HRMS 9.0
Page 140
Step Action
42. Click an entry in the Select Constant column.
Step Action
43. Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 141
Step Action
44. Click the Save button.
45. Click the Run tab.
46. Use the Run page to view the results of your query.
47. The query results display the temporary employee information. The results include the all the fields you selected on the Fields page.
48. You have successfully created a record hierarchy join.
End of Procedure.
Creating a Query Runtime Adding a prompt enables you to further refine a query when you run it. For example, suppose
you wanted to change a query so that you could prompt the user to enter a value for the duration
of a vacation. Prior to adding the prompt, the query always retrieved rows for employees who
have taken vacation based on a defined constant value on which to make a comparison. Adding a prompt to the query enables the user to enter any duration, then the query can return employees
based on the value provided when running the query.
When you run a query with a prompt, a dialog box appears for you to specify the required value.
Enter the value into the text box. The query uses the value that you enter as the comparison value
for the criterion that included the prompt.
If the field for which you are prompting has an associated prompt table (even if it is the Translate
table), the Edit Table drop-down list box shows its name.
Training Guide
Enterprise Query for HRMS 9.0
Page 142
In this topic, the Human Resources Department has requested a query that enables the user to
specify vacation type and employee ID at run-time.
Procedure
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Training Guide Enterprise Query for HRMS 9.0
Page 143
Step Action
2. Click the Query Manager link.
Training Guide
Enterprise Query for HRMS 9.0
Page 144
Step Action
3. Click the Create New Query link.
Step Action
4. Find the record to be used in your query, VAC.
Enter the desired information into the begins with field. Enter "VAC".
5. Click the Search button.
6. Use the Records page to view existing records to add new records.
7. Click an entry in the Add Record column.
Training Guide Enterprise Query for HRMS 9.0
Page 145
Step Action
8. A dialog box appears that indicates that the effective date criteria has been automatically added to this effective dated record.
Click the OK button.
9. Use the Query page to select fields to add to the query content or add additional
records.
Training Guide
Enterprise Query for HRMS 9.0
Page 146
Step Action
10. Click the EMPLID - EmpliID option.
11. Click the PLAN_TYPE - Plan Type option.
12. Click the BENEFIT_PLAN - Benefit Plan option.
13. Click the VACN_HOURS - Vacation Buy/... option.
14. Click the COVERAGE_ELECT - Coverage ... option.
15. Click the Fields tab.
16. Use the Fields page, to edit, delete, or add criteria to each field.
Training Guide Enterprise Query for HRMS 9.0
Page 147
Step Action
17. Click the Criteria tab.
18. Use the Criteria page to view any existing criteria for your query, and if necessary,
add or modify selection criteria for the query.
Training Guide
Enterprise Query for HRMS 9.0
Page 148
Step Action
19. Click the Add Criteria button.
20. Use the Edit Criteria Properties page to define the selection criteria for the query.
Training Guide Enterprise Query for HRMS 9.0
Page 149
Step Action
21. Add the prompt criteria for the Plan Type field.
Click the Select Record and Field button.
Training Guide
Enterprise Query for HRMS 9.0
Page 150
Step Action
22. Click the A.PLAN_TYPE - Plan Type link.
Training Guide Enterprise Query for HRMS 9.0
Page 151
Step Action
23. Click the Prompt option.
24. If you click New Prompt, you are taken to the Edit Prompt Properties page, on which you can create the prompt.
Click the New Prompt link.
25. Use the Edit Prompt Properties page to verify or select the parameters for the
Runtime prompt.
26. You can click the magnifying glass to select a prompt field. When accessing this
page from the Edit Criteria Properties page, the field is already populated based on the field selected on that page.
After you select a field, it shows the name of the field. Query looks to the record definition for information about this field and fills out the rest of the dialog box
based on its properties.
27. You can modify the Heading Type as desired:
• Rft Long: The long field name from the record definition. • Rft Short: The short field name from the record definition.
• Text: User defined.
28. Use the Type field to define the type of field edit for the specified field. PeopleSoft
recommends that you use the same Type that is used in the field's record definition so that the edit type is consistent throughout PeopleTools.
29. If the Edit Type is Prompt Table, the value in the list box specifies the prompt
table to use. If the Edit Type is Translate Table, the value in the Field list box
determines the values used. Query assumes that the specified field has Translate
Table values associated with it, and that the field is identified as a Translate Table field in its record definition.
Training Guide
Enterprise Query for HRMS 9.0
Page 152
Step Action
30. In this example, you will use all the default values for this field.
Click the OK button.
Training Guide Enterprise Query for HRMS 9.0
Page 153
Step Action
31. The prompt is now represented on the Edit Criteria Properties page as a bind variable, :1
Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 154
Step Action
32. Add the prompt criteria for the ID field.
Click the Add Criteria button.
Training Guide Enterprise Query for HRMS 9.0
Page 155
Step Action
33. Click the Select Record and Field button.
Training Guide
Enterprise Query for HRMS 9.0
Page 156
Step Action
34. Click the A.EMPLID - EmplID link.
Step Action
35. Click the Prompt option.
36. Click the New Prompt link.
Training Guide Enterprise Query for HRMS 9.0
Page 157
Step Action
37. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 158
Step Action
38. The prompt is now represented on the Edit Criteria Properties page as a bind variable, :2
Click the OK button.
Step Action
39. Click the Save As link.
40. Use the Enter a name to save this query as: page to name and describe your query.
Training Guide Enterprise Query for HRMS 9.0
Page 159
Step Action
41. Enter the desired information into the Query field. Enter "Vacation".
42. Click in the Description field.
43. Enter the desired information into the Description field. Enter "Vacation per
employee".
44. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 160
Step Action
45. Finally, view the results of the query.
Click the Run tab.
46. Notice that you are prompted to enter values before the query is run.
Training Guide Enterprise Query for HRMS 9.0
Page 161
Step Action
47. Click the Plan Typ list.
48. Click the Vacation list item.
49. For this example, run the query for Employee ID AA0004 and Plan Type vacation.
Click in the ID field.
50. Enter the desired information into the ID field. Enter "AA0004".
51. Click the OK button.
52. Use the Run page to view the results of your query.
53. The vacation type information is displayed for the employee.
54. If you ever need to modify the prompt criteria, you can use the Prompts page to do
that.
Click the Prompts tab.
55. Use the Prompts page to add, edit, and delete prompt criteria.
56. You have successfully created a query with runtime prompts.
End of Procedure.
Training Guide
Enterprise Query for HRMS 9.0
Page 162
Creating Record Joins When writing queries, it is fairly simple to retrieve information from one table. In many cases, you want to retrieve data from more than one table or specify criteria in your query from a second
table. In these cases, you need to link at least two tables in one query. Working with multiple
tables is almost as easy as working with one table.
A join enables you to retrieve data from two or more records or to specify criteria from more than
one record. Whenever you perform a join, you link records based on their common fields.
To assist users in using query joins, PeopleSoft delivers a number of predefined joins. There are
two types of predefined joins: hierarchical joins and related record joins. Because these types of
joins are predefined, you do not have to add any criteria to manually link the records.
Record Hierarchy joins have a one to many relationship. They use records that are parents or
children of each other. A child table is a table that uses all the same key fields as its parent, plus
one or more additional keys. The parent record in PeopleSoft Application Designer defines the hierarchical relationship.
Related Record joins have a one to one relationship. They use records from non-hierarchical records that are related by common fields. The prompt table edit defined for a field in PeopleSoft
Application Designer determines the relationship between the records.
In this topic, your company wants to review recent job applications on file. Create a query for all recent applicants including the applicant ID, application date, applicant's name, the company
location at which the applicant applied, and location description. To do this, you will have to join
two related records, HRS_APP_INFO_I and HRS_LOC_I.
Procedure
Training Guide Enterprise Query for HRMS 9.0
Page 163
Step Action
1. Begin by navigating to the Records page.
Click the Reporting Tools link.
Training Guide
Enterprise Query for HRMS 9.0
Page 164
Step Action
2. Click the Query Manager link.
Training Guide Enterprise Query for HRMS 9.0
Page 165
Step Action
3. Click the Create New Query link.
Step Action
4. Locate the first record to be joined.
Enter the desired information into the begins with field. Enter
"HRS_APP_CURR_I".
5. Click the Search button.
6. Use the Records page to review existing records to add a new record.
7. Click the Add Record link.
8. Use the Query page to add fields to your query.
Training Guide
Enterprise Query for HRMS 9.0
Page 166
Step Action
9. Click the APPLID - Applicant ID option.
10. Click the EMPLID - EmplID option.
11. Click the vertical scrollbar.
12. Click the Join HRS_LOC_I - Recruiting Location Area Intfc link.
13. Use the Select joint type page to select a standard join or a left outer join.
Training Guide Enterprise Query for HRMS 9.0
Page 167
Step Action
14. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 168
Step Action
15. Click the HRS_LOCATION_ID - Recruiting... option.
16. Click the DESCR - Description option.
17. Click the Fields tab.
18. Use the Fields page, you can edit, delete, or add criteria to each field.
Step Action
19. Edit the properties for the APPLID - Applicant ID field.
Click the Edit button.
20. Use the Edit Field Properties page to change the column heading and apply the
aggregate function to this query.
Training Guide Enterprise Query for HRMS 9.0
Page 169
Step Action
21. Click the Text option.
22. Click in the Heading Text field.
23. Enter the desired information into the Heading Text field. Enter "Applicant ID".
24. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 170
Step Action
25. Click the Save button.
26. Use the Enter a name to save this query: page to name and describe your query.
Training Guide Enterprise Query for HRMS 9.0
Page 171
Step Action
27. Enter the desired information into the Query field. Enter "APP_LOC_INFO".
28. Click in the Description field.
29. Enter the desired information into the Description field. Enter "Applicant Location
information".
30. The Query Type field enables you to choose from User Query Type, Process Query
Type, or Role Query Type. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types.
31. Click the Owner list.
32. Click the Public list item.
33. Click the OK button.
Training Guide
Enterprise Query for HRMS 9.0
Page 172
Step Action
34. Click the Run tab.
35. Use the Run page to view the results of your query.
36. In a related record join, you can automatically join two records based on a relationship that has been predefined by the record designer.
End of Procedure.
top related