Peoplesoft Query Tips

Owned by Stephen Ashby
Last updated: Mar 07, 2023 by Tammy Voigt

PeopleSoft Query Help:

 Oracle help pages:

Tips for Creating and Using Queries:

  • Remove “PS_” prefix when searching for Query Records/Tables to add to a new query: 
    • When searching for "Records" (aka: tables) to add as a data source for the query, if name starts with PS (e.g. PS_SU_AD_INFO), nothing appears in searches.  Exclude the PS prefix to locate a specific table/record by name. 
  • When creating a query, where possible, start by copying an existing query that contains related data.  Searching the list of existing public queries may return a dataset that already contains the information you need.   This will save time and keep you from having to locate data sources and join tables.
  • If you are unsure about how to run, schedule, or create a query, practice in the TEST instance of GullNet, available at: http://www.salisbury.edu/gullnet/dev/
  • Use wildcards to help when searching for existing queries, or tables.  Surrounding a search term with the % symbol/wildcard will ignore any characters before or after the key term.  Similarly, the underscore (_) wildcard can be used to ignore any single character.  E.g. When searching the list of existing public queries, the phrase %ADVISE% will find any query containing ADVISE in the title.
  • Knowing table names and where data resides will help when building or adding fields to an existing query.  If you do not know what records/tables to use when building queries, but you know the page location in PeopleSoft of the data you wish to pull, try the keyboard combination "Ctrl-shift-j" on that page to view corresponding data sources for the field(s) in question.   
  • Change column headings in a query.  Fieldnames often aren’t descriptive enough for a public facing report/query.  To add a label, select the "text" radio button at the top (instead of RTF short).  Heading text will often not save unless this is done. 
  • Query results can be saved as a file to the default directory: O:\GullJobData\prd -  Query manager allows users to specify a different folder for output.  
  • Dates/timestamp fields can be subtracted from one another using an expression, which can add a column showing the duration between 2 dates/timestamps (e.g. datediff(dd,B.DATE,B.SU_PUR_COMPLT_DATE) will subtract the first date from the last date, and return the number of days in an expression column).  
  • Line feeds, carriage returns, and commas can cause formatting issues when exporting a file as a CSV, which can cause issues with data visualization tools that might connect to the data file (e.g. Excel, PowerBI).  Expressions can be used to remove these characters from fields.  This sample expression removes commas, carriage returns (char(13)), and line feeds (char(10)) from a DESCRLONG_NOTES field: replace (replace (replace (B.DESCRLONG_NOTES, ',' , ''), char(13) , ''), char(10), '')
  • Concatenate related parent/child fields to create unique values in a column for data analysis. This sample expression combines two fields with a dash separator:  concat(B.PROJECT_ID,'-',B.SUB_PROJECT_NUM)
  • Schedule a query on some frequency:
    • Reports run/send immediately after setting schedule and saving.  Revising the recurrence may also trigger a run.
    • Remove pre-existing scheduled query processes when re-scheduling, or those older instances will keep running on a schedule.
    • If a specific recurrence option doesn’t exist in the pulldown (e.g. bi-weekly on XX day), please complete a ticket request online at https://support.salisbury.edu/ 



Did this solve your issue??