Query Manager Deep Dive for Technical and Functional Users

Harry E Fowler

4 years ago

At RECONNECT, State of Minnesota’s Rahul Nori provided a Query Manager deep dive for both technical and functional users. Nori covered basic tips and tricks for query writing, creating smart prompts, and using expressions wisely. He also covered what you can do to ensure that your system keeps your information safe and secure while keeping your database from spinning out of control.

PeopleSoft Query Manager Deep Dive

A relational database provides an efficient way to store, retrieve, and analyze data. It can be in several formats, including electronic, printed, graphic, audio, statistical, or a combination of two or more. Before databases were created, data was stored in flat files. A sample flat file looks like this:

It was difficult to search for specific data or to create reports. The solution was to organize the data into a table with columns—representing a certain characteristic—and rows—representing each individual entry for that data element.

A relational database in concept is structured to recognize relationships between items of information stored in the database. It contains a series of tables organized by key fields and unique IDs. Relational databases are created using a special computer language called Structured Query Language. Through SQL, logic tables are joined to make a single database.

For the key structure, you can have duplicates when there are multiple primary keys. However, putting the two keys together keeps from duplicates.

A basic query statement is Select [field(s)], From [table(s)], Where [criteria]. Queries can be used for Ad hoc reporting, scheduling, search, population selection, and data sources for communications, BI reports, and pivot grids.

Why do we query? We query to understand the data beneath the pages that we see in PeopleSoft. HCM and Financials have thousands of tables. They vary from very basic and single-use to ghost tables in FMS to temporary tables to custom tables. The tables are interconnected by PeopleCode, PeopleSoft’s proprietary coding language.

Basic Queries

The following is a simple select query, which selects three fields from the Employees table.

You can then apply conditional ﬁlters. In this case, the ﬁlter is City = London. You have the ability to set multiple ﬁlters of varying types.

Another capability is Order By, which allows you to organize the data based on alphabetical or numerical qualification. In the example, the data was re-ordered alphabetically by city.

An aggregate such as SUM can be achieved with a similar approach to this example:

SELECT SALESORDERID, SUM(LineTotal) AS SubTotal
FROM SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID

The data can further be limited with a HAVING clause such as HAVING SUM(LineTotal) > 100000.

When searching in PeopleSoft, use a % to replace any number of characters. For example, search WHERE(City LIKE ‘Lon%’) will return London and any other city that begins with Lon. To return items with only one additional letter, use an underscore, such as FirstName LIKE ‘Laur_’).

REGEXP_LIKE performs regular expression matching instead of the simple pattern matching. For example, WHERE REGEXP_LIKE (first_name, ‘^Ste(v|ph)en$’) returns Steven and Stephen.

Find Component Navigation

Most PeopleSoft customers will benefit from learning how to find component navigation. If you’re a developer who receives emailed screenshots, but with no instructions on how your co-worker arrived at the message, you can use the following approach:

PeopleSoft delivered a page that allows you to find component navigation. Go to Find Object Navigation, plug in your data, and the navigation will show up.

Joins

Joins are important, as well. There is always confusion about what you aren’t getting from a join.

These are the possible types of joins:

PS Query Tab-By-Tab

The navigation for Query Manager is Root > Reporting Tools > Query > Query Manager

The Records tab gives you the ability to search for your records.

The Query tab allows you to pick all the ﬁelds that you would like to display.

The Expressions tab is a means of deﬁning your SQL statement, speciﬁcally with your Select and Where statements.

The Prompts tab allows prompts or bind variables. Prompts are completely user conﬁgurable. You can deﬁne the length, format, edit type, and specify a default value.

The Fields tab allows you to deﬁne the display information for your ﬁelds. Based on the tables you choose, these will be deﬁned for you. However, they are conﬁgurable. You can reorder and sort ﬁelds.

The Criteria tab allows you to add, edit or group criteria. You can also edit auto-join criteria. Additionally, you can subquery with exists clauses.

You can do standard joins to as many tables as you want. PS Query will suggest Auto Join Criteria based on ﬁeld name.

Left Outer Joins add as a ‘belongs to.’ To outer join, change each of your join criteria to [ﬁeldname]. When adding criteria for Left Outer Joins, make sure you set the criteria as ON clause of outer join. Otherwise, the criteria will cause your join to act as a standard join.

The Having Tab is only used with aggregate ﬁeld functions if you have an aggregate ﬁeld that you want to use in criteria.

Once you are ﬁnished with all of this, go to the View SQL tab to check your work. It is always a good idea to see how the criteria you add factor into the SQL statement. You are not able to modify the SQL here, but you can read it and write it in SQL developer. You can select the XLAT/Related Language box to display the full SQL. Leave it unchecked to display SQL without the XLAT and Related Language joins.

Finally, the Run tab is where you will run the query. Once run, output data can be downloaded to an excel document. Rerun your query to enter new bind variables (prompts values). If you have trouble downloading to XL, you may want to save it as a CSV.

Make sure to save your work regularly so that you do not lose it. Public queries are available to everyone while private queries will give you sole access to the query.

Expressions that are commonly used include the following:

ROUND – Rounds a decimal number to a specified number of digits
- ROUND(12.3456,1) =12.3
- ROUND(12.3456,2) =12.35
SQRT – Provides the square root of the number; POWER and EXP work in the same manner
- SQRT(144) = 12
CEIL – Provides the largest integer greater than or equal to a decimal number
- CEIL(123.45 = 124
REGEXP_COUNT – Provides a count of characters matching the regular expression; lowercase vowels in the example
- REGEXP_COUNT(‘Anderson’, ‘a|e|i|o|u’) = 2

String concatenation is commonly used when you want to concatenate first name and last name. You can use the pipeline symbol: | | or CONCAT. The syntax of CONCAT as a function accepts only two strings at a time.

‘People’ | | ‘Soft’ = ‘PeopleSoft’
CONCAT(CONCAT(A.LAST_NAME, ‘,’), A.FIRST_NAME) = ‘Doe, John’

You can also use functions for string manipulation, as for returning string length, trim, NVL, NVL2, and data masking.

Date Formatting can be done in Query Manager. The most commonly used date formatting is TO_CHAR to specify the format of the date or the formatting of numbers. TO_DATE and TRUNC can also be used.

TO_CHAR(sysdate, ‘yyyy/mm/dd’) gives a result such as ‘2003/07/09’
TO_CHAR(to_char(‘1000’, ‘$9,999.99’) gives the result $1,000.00

Logic and translation can be used with DECODE. DECODE is basically an IF, THEN statement. You can also use CASE to make more complex decisions using multiple fields.

DECODE(A.Day, ‘Sun’, ‘Sunday’, ‘Sat’, ‘Saturday’, ‘Weekday’) will return a value of ‘Weekday’ if anything other than ‘Sun’ or ‘Sat’ are the values.

Meta_SQL can be used in expressions to retrieve current dae, current date time, operatorID, the difference between dates, etc.

Regular expression is used to replace. In the following example REGEXP will replace certain HTML tag occurrences with ‘ ‘ — eﬀectively removing them.

Aggregating Field Values can be used to edit fields. Examples are sum, count, distinct count, minimum, maximum, and average.

ListAgg is an expression that combines the data from a given field in one row, partitioned by however you decide to group it, rather than returning data in multiple rows.

Aggregate Values – Analytics

Sum will return one value. Sum with GROUP BY will return multiple values, dep3ending on the number of rows that were selected. There will be one SUM value returned per row.

Rollup does the same thing, but for Fact ID 1, it will give you a value, and for Fact ID 2, it will give you another value. Cube takes this one step further.

Subqueries

A query within query, also called a “sub-select” is a subquery, For our purposes, we will use it to filter. A subquery can also be part of the select clause as a field-delivered outer join. A subquery is helpful in the following cases:

Pick one row out of multiple rows
In List Queries
Not Exist Queries

Unions

Unions are possible in PS Query. Union queries allow merging data from 2 query sets when it is impossible to get data from one. For example, you can fetch a list of admitted applicants and enrolled students.

Create a query for admitted applicants.
Click “New Union” at bottom of page
Create another query for enrolled student query
Have to use all the same columns

Make sure to use the subquery/union navigation link and save the query before running.

Drilling URLs

Drilling URLs are important for queries with hot links. You will create an expression and the expression type will be Drilling URL. The most commonly used one is Component URL. External URL and Free Form URL are also available. URL search keys pre-populate the search area where you would typically enter data, with the date from your query. The links that show up will be a slightly different color than the remainder of the text to indicate they are links.

Query Performance Optimization Tips

The ﬁrst step in optimizing performance is to check Security Join Optimizer. For a user at UT Dallas, checking this box reduced run time on a query from 2 hours to under 5 seconds. This is not recommended for left outer joins.

You can also use Database Hints as expressions. Optimizer hints can be used with SQL statements to alter execution plans. One example is /*+BIND_AWARE*/. Make sure to work with your DBAs. Additionally, you can set a trace for your aﬀected query to troubleshoot.

Query Administration and Security

You can administer queries at PeopleTools > Utilities > Administration > Query Administration.

An example use case for this would be if an employee retired who had hundreds of queries that were only accessible to him. You can use Query Admin to change all of his queries from private to public. This feature is run by PeopleSoft Security and is not part of the Report Manager menu structure.

On the executing tab, you can find all the queries that have been running longer than (n) minutes and kill them.

On the permission list, there is a query proﬁle link. In this link, you can pick options for the user.

Tips and Tricks

The following are tables that exist in case you need quick access to them:

If you are using an Oracle database, checking ‘distinct’ may cause the following error if you are trying to display a long description or other ﬁeld that can contain a large number of characters:

To correct it, do one of the following:

Uncheck ‘distinct’
SUBSTR(TRIM(YOUR_FIELD), 500, 1)
TO_CHAR(YOUR_FIELD)

Conclusion

Queries can be used for many reasons. It is easiest to understand their many functions by creating specific use cases. It is recommended to run queries for simple equations, not complex problems. Leverage Query Manager and the above examples to steer your business into greater simplicity and increased efficiency.