- Managing PS Queries
- Query Statistics
- Find Custom PeopleSoft Queries
- Embedded SQL in PeopleSoft Query
- Writing a Date Expression
Managing PS Queries
There is a way to delete and rename queries as well as to change them from private to public queries by clearing the owner ID.
However, it is buried away in another part of the PeopleTools navigation:
To modify a query, first perform a manual search for it:
You can then select it to modify or rename it. To make it public, click on Assign New Owner and clear out the owner to make it public.
PeopleSoft provides the ability to track query statistics for PeopleSoft Queries such as the number of times the query has been executed, the average execute time, average fetch time, average number of rows returned, the last time it was executed and the number of times it has been killed.
This is all very useful information for two major things; evaluating query performance and determining how often a query is being used (e.g. when you are evaluating keeping the query on an upgrade or patching project).
To enable query statistics / view query statistics the navigation is:
The Run Query Statistics option needs to be set for PeopleSoft to start recording and keeping query statistics.
Once enabled, use the same navigation to view query statistics.
The executing tab allows you to see which queries are running at the moment.
Behind the scenes, you can view this data in the table,
Find Custom PeopleSoft Queries
The following SQL identifies custom queries created in your system from the
PSQRYDEFN PeopleTools table. It includes the query type (and the operator ID who owns the query if it is private).
select QRYNAME as "Query Name", DESCR as "Query Description", CREATEOPRID as "Created By", CREATEDTTM as "Created On", LASTUPDDTTM as "Last Updated By", LASTUPDOPRID as "Last Updated On", case when OPRID != ' ' then 'Private - ' || OPRID else 'Public' end as "Query Type", VERSION as "Revisions", QRYAPPROVED as "Query Approved?", APPROVEOPRID as "Approved By", APPROVEDTTM as "Approved On" from PSQRYDEFN where LASTUPDOPRID != 'PPLSOFT'
Note to run on a SQL server database you'll need to change the
|| concatenation operator to a plus sign (
Embedded SQL in PeopleSoft Query
Turns out that you can embed SQL into your PeopleSoft Query using the Meta-SQL operator,
%Sql. Here's a simple example using the information provided in the Case When Exists SQL article.
Basically, we want to turn the following SQL into a PeopleSoft Query:
elect O.OPRID, O.EMPLID, case when exists ( select 1 from PS_PERSON P where P.EMPLID = O.EMPLID ) then 'Person' else 'Not a Person' end as PERSON_STATUS from PSOPRDEFN O;
The catch is that we need the
case-when-exists block as a sub-select and you can't have a from clause in an expression. So here are the steps:
- Create a new query
- Add the record
- Add the fields
This gives us everything except for the
Open application designer, and create a new SQL definition. Add the following SQL to the definition:
case when exists ( select 1 from PS_PERSON P where P.EMPLID = A.EMPLID ) then 'Person' else 'Not a Person' end as PERSON_STATUS
Save the definition with an appropriate name (e.g.
Go back to your query:
- Add an expression, make the length 12 characters (length of the words Not a Person)
- In the expression text, type
%SQL(DETERMINE_PERSON_STATUS)(the meta-sql to expand the SQL you created)
- Click the Use as a Field hyperlink to add the expression as a field
- In the Field tab, click on the expression and change the heading text to something more friendly, e.g. Person Status.
View your SQL to confirm it shows up with the
Run the query (you may exceed the results set) but you should see the Person Status field with the value of either
Not a Person depending on whether or not they have an ID.
Writing a Date Expression
The following example returns the processing time for a process/job in the process request table. This query returns the process instance, process name, operator ID, run status, run control ID and processing time (which is process end date/time - process begin date/time expressed as seconds).
This is how the expression looks (type character).
The key to this is the following:
to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60))
This gives the difference in seconds. However if you just want the answer in days it would be just:
to_char(round(ENDDTTM - BEGINDTTM))
Here's the Query SQL:
SELECT A.PRCSINSTANCE , A.PRCSNAME , TO_CHAR(A.RUNDTTM ,'YYYY-MM-DD-HH24.MI.SS."000000"') , A.OPRID , A.RUNSTATUS , A.RUNCNTLID , (CASE WHEN ENDDTTM IS NULL THEN 'Not Available' ELSE to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60)) END) AS PROCESSING_TIME FROM PSPRCSRQST A