Query

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:

PeopleTools > Utilities > Administration > Query Administration

To modify a query, first perform a manual search for it:

manual-query-admin-search.png

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.

assign-blank-query-owner-to-make-it-public.png

Query Statistics

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:

PeopleTools > Utilties > Administration > Query Administration

The Run Query Statistics option needs to be set for PeopleSoft to start recording and keeping query statistics.

run-query-statistics.png

Once enabled, use the same navigation to view query statistics.

This is also where you can change private queries to public queries or vice versa:
Admin tab > Assign New Owner

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, PSQRYSTATS.

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:

This gives us everything except for the case-when-exists block.

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. DETERMINE_PERSON_STATUS).

Go back to your query:

View your SQL to confirm it shows up with the case-when-exists block.

Run the query (you may exceed the results set) but you should see the Person Status field with the value of either Person or Not a Person depending on whether or not they have an ID.

NOTE: you need to explicitly code in the join in the DETERMINE_PERSON_STATUS SQL - the line where P.EMPLID = A.EMPLID. The alias, A may be different depending on the table you are joining too.

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).

processing-time-query-expression.png

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))
Note the to_char is to format the output in character format which PS Query seems to handle a lot better than numeric format.

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