Queries

PeopleSoft Queries related articles.

PeopleSoft SQL Snippets

PeopleSoft specific SQL snippets. Fire away!

SQL & PL/SQL

This is a handbook to organize SQL snippets/sample code. Snippets included in this handbook range in difficulty from simple short SQLs to fairly complex ones.

I encourage you to look at current published snippets and try to enhance them by using the comment form at the end of each post. There is always a different/better way of doing things!

Check What Record Has Your Fields

This is another quick SQL snippet that I find useful. When would you use it?
Well, you forgot a certain record name, but you know that the record has EMPLID, NAME, EMPL_REC fields on it.
You can query the database to find out what records have the fields you are looking for.

SELECT RECNAME, COUNT(FIELDNAME)
FROM PSRECFIELDALL
WHERE FIELDNAME IN ('EMPLID', 'NAME', 'EMPL_RCD')
GROUP BY RECNAME
HAVING COUNT(FIELDNAME) = 3;

Get all Components Under a Certain Portal Folder

Use this SQL snippet to return all components plus sub-folders (if any) under a specific portal folder.

For example, lets say you have a folder called "Workforce Admin". Under Job Information, you have a sub-folder called "Job Information" and few components.

Run the SQL against the Workforce Admin folder and you should get a row for the sub-folder, and a row for each of the components.

-- Get all sub-folders and components under a certain folder name --
SELECT PORTAL_NAME,
   PORTAL_PRNTOBJNAME AS FOLDER,
   PORTAL_OBJNAME AS CONTENT_REFRENCE,

Component, Content Reference, and Menu

Ok, so we all know that a component needs to be attached to a menu before you are able to register it to the portal structure. Once a component is registered using Application Designer, then a content reference is created and your component will be available under the folder and path you specified during the registration process.

Query for Component Path within PeopleSoft Portal

While back, I've created an SQL to find the exact same thing but with some level limitations. I have came across another query written by Jim's PeopleSopft Journal blog that works a little bit better and thought to share it with my visitors.

Jim's query however requires the developer to know the content reference of a component before the query could be used. What I really wanted is the ability to find the path by just knowing the component and not the content reference. Chances are, if you know the component reference you know the path to that component as well. So, to find the component path without knowing the content reference, follow the steps below:

1) Run the below SQL to get the content reference name for your component

SELECT PORTAL_NAME,
 PORTAL_OBJNAME AS CONTENT_REFERENCE,
 PORTAL_LABEL,
 PORTAL_URI_SEG1 AS MENU,
 PORTAL_URI_SEG2 AS COMPONENT,
 PORTAL_URI_SEG3 AS MARKET
FROM psprsmdefn
 WHERE PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_URI_SEG2 = :1;

-- Replace :1 with the component name you are looking for.

Using Hyperlinks in Queries to Drill Down to Individual Values

I came across a PeopleSoft posting written by Rob's PeopleSoft Blog and could not help but to post it on my blog. It is a neat trick and I'm sure will come handy in many occasions!

SQL to Check PeopleSoft Process Name Status

Run this SQL on a particulare process name to get it's process status form PeopleSoft process request.

SELECT a.prcsname, a.oprid, a.servernamerun, b.xlatlongname AS STATUS,
a.rundttm,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24, 24)) "Hr",
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60, 60)) mi,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60 * 60, 60)) sec,
a.prcsinstance, c.outputdir
FROM psprcsrqst a, xlattable b, ps_cdm_list c
WHERE b.fieldname = 'RUNSTATUS'
AND b.fieldvalue = a.runstatus
AND a.prcsinstance = c.prcsinstance
AND a.prcsname = 'YourProcessName'