Advanced PS Query

PeopleSoft Query Training

Advanced
August 2018

1
Agenda
 Creating Expressions
– Build Expressions
– Add Expressions to Query
– Add Expressions in Criteria
– Add Prompt in Expressions

 Using Subqueries
– Explaining Subqueries
– Creating a Subquery
– Edit a Subquery

 Using Summary Calculations

– Understanding Aggregate Functions
– Using the Having Criteria

 Joining Records
– Describing Joins
– Creating Left Outer Joins

 PeopleSoft Query Administrative Tasks

– Running a Query from Query Viewer
– Scheduling a Query

 Explain other PeopleSoft Query functions

– Working with Unions
– Understanding Group By criteria

2
Creating Expressions - Build 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.

3
 Creating Expressions - Build Expressions

• Navigate to : Reporting
Tools  Query  Query
Manager

• Click the Query Manager

menu

4
 Creating Expressions - Build Expressions

• Click the Create New Query

link

5
 Creating Expressions - Build Expressions

The first step in creating a query is to find an existing record for the query. In this example , you want to locate and use the
ADDL_PAY_RECORD

• Enter the desired

information into the
description field.

• Enter a valid value

e.g. “ADDL_PAY_DATA”

• Click the Search button.

6
 Creating Expressions - Build Expressions

• Click the Add Record link

7
 Creating Expressions - Build Expressions

• When a record contains

an Effdt, the appropriate
criteria is added to your
Query

• Click the OK button

8
 Creating Expressions - Build Expressions

• Check fields required for

the query report

9
 Creating Expressions - Build Expressions

• Click Fields Tab

10
 Creating Expressions - Build Expressions

• Click Fields Tab

11
 Creating Expressions - Build Expressions

• Click Edit button for

ADDLPAY_REASON to
select Xlate value

12
 Creating Expressions - Build Expressions

• Select RFT Long for

Heading
• Select Long for Translate
Value
• Click the OK button

13
 Creating Expressions - Build Expressions

• Next, you need to add the

expression to divide Oth
Pay to get a quarterly
amount

• Click the Expressions tab

14
 Creating Expressions - Build Expressions

• You will create expression

statements by selecting
operators, fields and
constant values

• Click the Add Expression

button

15
 Creating Expressions - Build Expressions

Use the Edit Expression Properties page to select expression type, length, decimals(for Number and Signed Number) and to
enter expression text

• Select Expression Type –

e.g. Number
• Enter Length – e.g. 8
• Enter Decimals – e.g. 2
• Check Aggregate Function
to create an aggregate
function such as Sum,Avg,
or Count. In this example
do not select this function
• If you know the field name,
you can enter it in
Expression Text.
Alternatively you can click
Add Field link to add a field
to this expression.
• Click the OK button

16
 Creating Expressions – Add Expression to Query

• Click Use as a field

link to display the
result in query output

17
 Creating Expressions – Add Expression to Query

An expression can be treated just as 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 to text of the
expression field

• Click the Edit button

18
 Creating Expressions – Add Expression to Query

• Click in the Heading Text

field.

19
 Creating Expressions – Add Expression to Query

• Enter the desired name

into the Heading Text
field.
e.g.“Quarterly Other Pay”
• Click the Query Manager
menu
• Click the OK button

20
 Creating Expressions – Add Expression to Query

• Click the Save button

21
 Creating Expressions – Add Expression to Query

• Enter a valid value for

Query field.
e.g. “Expression”
• Enter a valid value for
Description field.
e.g. “Expression Example”
• Query type User is used for
standard queries. Process
or Role types are used for
Workflow queries.
e.g. Select User in this
example
• Owner Private indicates
that only you can access or
change this query, Public
indicates that any user can
access or change this
query.
e.g. Select Private in this
example
• Enter a meaningful
description for Query
Definition
• Click the OK button

22
 Creating Expressions – Add Expression to Query

• Finally, view the results of

the query.
• Click Run tab

23
 Creating Expressions – Add Expression to Query

The results display the net and quarterly Other Pay amounts for each employee.

You have successfully defined an expression and added to the query.

24
 Creating Expressions – Add Expression to Criteria

• Select Criteria Tab

• Click Add Criteria

• Select Expression for

Expression 1 Type
• Click on Search glass in
Expression

25
 Creating Expressions – Add Expression to Criteria

• Select Expression
A.OTH_PAY/4

26
 Creating Expressions – Add Expression to Criteria

• Select Condition Type

“greater than”
• Select Expression 2 Type
Constant
• Enter 100 for Constant

• Click the OK button

27
 Creating Expressions – Add Expression to Criteria

• Click the Save button

• Select Run tab

28
Creating Expressions – Add Expression to Criteria

The results display the net and quarterly Other Pay amounts for each employee greater than 100.

You have successfully defined an expression and added to the criteria.

29
 Creating Expressions – Add Prompt in Expressions

• Select Prompt tab

• Click Add Prompt

• Select NUM1 field

• Change Heading Text to

Enter Number

• Click the OK button

30
 Creating Expressions – Add Prompt in Expressions

• Select Expressions Tab

31
 Creating Expressions – Add Prompt in Expressions

• Click the Edit tab

• Select Add Prompt

• Select a Prompt
in this case :1

• Click the OK button

32
 Creating Expressions – Add Prompt in Expressions

• Click the Save button

• Select Run tab

33
 Creating Expressions – Add Prompt in Expressions

• Enter Number 4

• Click the OK button

34
 Creating Expressions – Add Prompt in Expression

The results display the net and quarterly Other Pay amounts for each employee.

You have successfully defined an expression and added to the query.

35
 Using Subqueries - Explaining Subqueries

Subqueries
Let’s review first! Remember your algebra equations from school? The expression (x+1)y is
indicating that x+1 needs to be solved before multiplying the result by y. The equation inside the
parentheses needs to be executed, then applied to the operation outside the parentheses.

A subquery, sometimes called a sub-SELECT, is a query whose results are used by another query.
The main query uses the subquery’s result set as a comparison value for a selection criterion.

You create a subquery when you need to compare a field value to the results of a second query.
Suppose, for example, that you want a list of employees with active child support garnishments. For
each employee in the PERSONAL_DATA table, you must determine whether his or her employee ID is
in the GARN_SPEC table. That is, you must compare the value in the PERSONAL_DATA.EMPLID field
to the results of a subquery that selects the EMPLID values from the GARN_SPEC table.

36
 Using Subqueries – Creating a Subquery

• Create New Query

• Select record name
PERSONAL_DATA and Add
Record
• Select required columns
ex. Emplid, Name,
Address1, City, State,
Postal
• Select Criteria tab
• Click the Add Criteria

37
 Using Subqueries – Creating a Subquery

• Check Field for Expression

1 Type
• Select A.EMPLID for
Expression 1
• Select in list for Condition
Type
• Choose Subquery for
Expression 2 Type
• Click Define/Edit Subquery

Note: For Condition Type in subqueries, selecting

• equals to is asking for a 1 to 1 relationship in the comparison,(not equal to exclude rows)
• in list is asking for a 1 to many relationship in the comparison (not in list is used to exclude rows),
• exists is asking for a 1 to many comparison where if the condition is satisfied in the subquery, show
results in the top level query (not exists is used to exclude rows).
If you are unsure about the relationship, it is recommended to use in list or not in list as the Condition
Type.

38
Using Subqueries – Creating a Subquery
Different Condition Types explained

39
 Using Subqueries – Creating a Subquery

• Enter Record Name

GARN_SPEC
• Click the Search button
• Select Add Record

40
 Using Subqueries – Creating a Subquery

• Click on Select link from

EMPLID

41
 Using Subqueries – Creating a Subquery

• Select Criteria tab

• Click the Add Criteria

42
 Using Subqueries – Creating a Subquery

• Check Field for Expression

1 Type
• Select B.GARN_STATUS
• Click Show Fields for
GARN_SPEC record
• Select B.GARN_STATUS
• Click the OK button

43
 Using Subqueries – Creating a Subquery

• Select a Constant C for

child support
• Constant C can be entered
directly or selected
through Search button
• Click the OK button

44
 Using Subqueries – Creating a Subquery

• Save Query with a meaning

full name
• Click Run tab

45
 Using Subqueries – Creating a Subquery

The results display employees with child support garnishments.

You have successfully created a subquery using in list.

46
 Using Subqueries – Edit a Subquery

• Select Criteria tab

• Click Subquery/Union
Navigation
• Select Subquery for
A.Emplid link

47
 Using Subqueries – Edit a Subquery

• Select Add Criteria

• Add new criteria to limit
to Company 001

48
 Using Subqueries – Edit a Subquery
Navigate to Top Level of Query

• Make sure Working on

selection is Subquery
• Select Subquery/Union
Navigation link
• Select Top Level of
Query

• Make sure now

Working on selection is
Top Level of Query
• Add/change criteria as
required
• Click Run tab

49
 Using Subqueries – Edit a Subquery

The results display employees with child support garnishments in Company 001.
You have successfully created a subquery using in list for company 001.

50
Summary Calculations - Aggregate Functions

 In a standard query, each row in the result set corresponds to an

individual row in the table that you are querying. Sometimes,
however, you instead want a summary of the information in
multiple rows. For example, you might want to know how many
customers you have in each state. You can query for this kind of
summary information using aggregate functions.
 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.

The following table lists the aggregate functions that you can apply to a field using PeopleSoft Query.

51
Summary Calculations - Aggregate Functions
- Count explained

Create a Query to count no of employees by Status in each Location

• Create a New Query

using Job record with
fields Location, HR
Status, Emplid and
Hourly_Rt
• Select fields tab
• Select Edit button for
field A.EMPLID
• Check Text and No of
Employees in
Heading
• Check Count in
Aggregate box
• Click the OK button

52
Summary Calculations - Aggregate Functions
- Sum explained

• Select Edit button for

field A.HOURLY_RT
• Check Text and enter
Sum of Hrly Rate
• Check Sum in
Aggregate box
• Click the OK button

53
Summary Calculations - Aggregate Functions
- Sum explained

• Save Query with a

meaning full name
• Select Run tab

54
 Summary Calculations - Aggregate Functions

The results display Location with no of employees and sum of hourly rate by Hr Status
You have successfully created a query using Aggregate functions Count and Sum.

55
Summary Calculations - Using the Having Criteria

PS Query/SQL does not support the use of aggregate functions in WHERE clauses. Therefore, after you have applied an
aggregate function to a field, you cannot use that field in your selection criteria, which corresponds to a SQL WHERE
clause. When you want to select rows based on the results of an aggregate function, Query Manager enables you to
create HAVING criteria. You might use such criteria, for example, when you want a list of location with more than 200
active employees.

• Select Having tab

• Click Add Having
Criteria button

56
Summary Calculations - Using the Having Criteria

• Check Field for

Expression 1 Type
• Search Fieldname for
Expression 1

57
Summary Calculations - Using the Having Criteria

• Select Condition Type

greater than
• Select Constant for
Expression 2 Type
• Enter 200 for
Expression 2
• Click the OK button

58
Summary Calculations - Using the Having Criteria

• Save the query with a

meaningful name
• Select Run tab

59
 Summary Calculations - Using the Having Criteria

The results display Location with no of employees greater than 200 and sum of hourly rate by Hr Status = Active
You have successfully created a query using the Having Criterai

60
Joining Records – Describing Joins

• Query Manager 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. PeopleSoft Query links the tables, based on common
columns, and links the rows on the two tables by common values in the shared columns.

• Joins are what make relational databases relational. Using joins, you define relationships among fields when you
query the records, not when you create the records. Because PeopleSoft records are highly normalized (they
each describe one kind of entity), you can easily use Query Manager to create joins.

• The procedure for joining tables differs depending on how the tables that are being joined are related to each
other. Query Manager recognizes three types of joins: record hierarchy, related record, and any record.

• This section discusses how to:

• Create record hierarchy joins.
• Create related record joins.
• Create any record joins.
• Create outer joins.
• Create left outer joins.

61
Joining Records – Describing Joins

Creating Record Hierarchy Joins

• A record hierarchy join 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.)
This example shows hierarchy joins for PERSON record:

62
Joining Records – Describing Joins

Creating Related Record Joins

• In a related record join, you can automatically join two records based on a relationship that has been predefined
in the record designer. For example, if a field has a prompt table defined for it, PeopleSoft Query displays a join
link to the right of the shared field.
The following example shows that you can join the Job table with Dept_Tbl record by using the DEPTID field:

63
Joining Records – Describing Joins

Creating Any Record Joins

Using Query Manager, you can create a join between two records (any record join) by selecting your initial base
record, defining its output fields and associated criteria, and then returning to the Records page to select the
second record. When you return to the Records page, you see the link Join Record rather than Add Record to the
right of all listed record names.
If you have the Enable Auto Join preference selected in the Query Preferences page, PeopleSoft Query
automatically attempts to join the new record to the existing record by looking for matching columns on the two
records.
To access the Query Preferences page, click the Preferences link on any page of Query Manager.

64
Joining Records – Creating Left Outer Joins

• Left outer join forces a row from one of the participating tables to appear in the result if no matching row exists.
• PeopleSoft Query enables you to easily create a left outer join.In a left outer join, all rows of the first (left)
record are present in the result set, even if no matches are in the joining record.
This example you will use records Addresses and Phone to report Home Addresses and Home Phone

• Create new query and select

record Addresses and select
required fields e.g. EMPLID,
ADDRESS1, CIRY , STATE
and POSTAL
• Select Records tab

65
Joining Records – Creating Left Outer Joins

• Select Record tab and enter

PERSONAL_PHONE
• Click the Search button
• Select Join Record
• Select Join Type Join to get
additional fields only (Left
Outer Join)
• Select A = Addresses for Join
Record

66
Joining Records – Creating Left Outer Joins

• Select A.EMPLID = B.EMPLID

• Click Add Criteria button

67
Joining Records – Creating Left Outer Joins

• Select fields PHONE_TYPE

and PHONE
• Click + button for
PHONE_TYPE

68
Joining Records – Creating Left Outer Joins

• Save query with a meaningful

name
• Select Run tab

69
Joining Records – Creating Left Outer Joins

• Check Constant for

Expression 2 Type
• Select Home for Expression
2
• Select ON clause of outer
join B for This criteria
belongs to
• Click the OK button

70
 Joining Records – Creating Left Outer Joins

The results display Employee Home Address and Home Phone if exist.
You have successfully created a query using left outer join.

71
 Query Administrative Tasks – Running a query from Query Viewer

• Navigate to : Reporting
Tools  Query  Query
Viewer
• Enter Query Name
LEFT_OUTER_JOIN
• Select Excel link

• Select Open

Results are opened in Excel

72
Query Administrative Tasks – Schedule Query

• Navigate to : Reporting
Tools  Query  Schedule
Query
• Enter Query Name
LEFT_OUTER_JOIN
• Enter Description Test

• Click the OK button

• Verify results from process

monitor

73
 Working with Unions

Unions enable you to get the results from two or more

separate queries at the same time. You can create an union
of multiple queries only when the queries have the following
common elements:
• The same number of selected fields.
• The same data types for all fields.
• The same display order for the columns.

UNION can also be a result set of results sets of two queries. ALL in the SELECT statement
includes duplicate rows. Column name comes from first table column name

Example:
Show me regular earnings for a paygroup.

74
Working with Unions

• Save Query and run

• Select record
PAY_EARNINGS and fields
COMPANY, PAYGROUP,
PAY_END_DT,
ERNCD_REG_HRS,
REG_HRLY_EARNS
• Select SUM for
REG_HRLY_EARNS
• Select New Union link

• Select records
PAY_EARNINGS and
PAY_OTH_EARNS
• Select fields COMPANY,
PAYGROUP, PAY_END_DT,
ERNCD, OTH_EARNS

75
Working with Unions

Results

Subquery/Union Navigation link used for toggling between Top Level of Query and Union1 records

76
 Understanding Group By criteria

On some occasions , you will need to write a query that returns rows that meet some criteria, but not all of them. For example , you
may be asked to create a query to list Employees either hourly or with standard hours 20.

• Create a New Query using

JOB
• Add criteria for
EMPL_TYPE = H OR
STD_HOURS = 20
• Select Group Criteria

• Enter Left Paren ( for

EMPL_TYPE Criteria
• Enter Right Paren ) for
STD_HOURS Criteria
• Click the OK button

• Now the criteria for

EMPL_TYPE and
STD_HOURS is Grouped

77
Demo

78
Questions

79