PeopleSoft Query Tables – The Complete List with Explanations

Peoplesoft Query Tables
As PeopleSoft is a metadata-driven application, all the building blocks including PeopleCode are stored within database tables.

And PS Query is no exception to this.

Knowing the tables underlying PS Queries will be handy when you have to write a query to fetch some details about Queries; for example, to see what other queries are there at your organisation that match a certain pattern or what other queries query a particular table.

In this post let’s see which are the meta tables that are used by PeopleSoft Queries.

Record Name Description Remarks
PSQRYDEFN Query Definition The main Query table.
PSQRYDEFNLANG Query Definition Alt. Language for PSQRYDEFN The related language record to the PSQRYDEFN table.
PSQRYRECORD Query Record All the Records that are used in the Query are stored in this record.
PSQRYFIELD Query Field All the fields that are used in the Query are stored in this record.
PSQRYFIELDLANG Query Field Alternate Language for PSQRYFIELD This is the related language for PSQRYFIELD.
So this comes to play only when you have a multi-language installation.
This record allows the Heading text of the fields to be translated.
PSQRYCRITERIA Query Criteria All data represented on the Criteria page of the Query are stored in this record.
PSQRYBIND Query Prompt Data underlying the Prompt page of the Query comes from this record.
PSQRYBINDLANG Query Prompt Alternate Lang. for PSQRYBIND This is the related language for PSQRYBIND.
So this comes to play only when you have a multi-language installation.
This record allows the Heading text that appears on the Query Prompt dialog to be translated.
PSQRYEXPR Query Expression Stores data underlying the Expression page of the Query as well as expression-based criteria.
PSQRYSELECT Query Select Stores a summary of the Query like how many sub-queries there are, how many selects there are, how many
criteria there are etc.
PSQRYXFORM Query Transformation Stores details entered on the Transformations page of the Query. So if you are looking at querying the
Transformation XSL, this is where you would be looking.
PSQRYFIELDDEP Query Field Dependency Stores the Field dependency details entered on the Dependency page of the Query. These settings are only
used for SQL pruning in Composite Queries.
PSQRYSTATS Query RunTime Statistics Stores Runtime statistics like the average number of fetched, how many times the query was executed etc.
PSQRYEXECLOG Query RunTime Log Stores information on the execution of queries including the date/time stamp of execution, the user who ran
it, time taken for execution, number of rows fetched etc.
PSQRYDEL Query Definition This record keeps track to deleted Query Names and their version numbers.
This is used by the VERSION App Engine.
PSQRYFAVORITES Query Manager Favorites Table If you add a Query to your favourites on the Quey Manager, it will be saved in this record.
PSQRYFLAGS Query Global Flags Table The Settings page of the Query Administration component available at PeopleTools > Utilities >
Administration > Query Administration
PSQRYLINK The relationships to child queries are maintained in this record.
PSQRYPREFS By looking the fields, this record seems to track if Auto-join is enabled and the name style (Description
vs Name and Description). But I haven’t seen any entries in this table.
PSQRYTRANS Each query that is run from Query Manager/Query Viewer is represented as a row in PSQRYTRANS.
This is where the Query monitoring tool looks for orphaned rows to kill the query.
PSQRYACCLSTRECS Query Access Record List Populated by the Query Access List process.
Stores details of which permission list has access to what records.

Apart from these standard query tables, there are a few more that are relevant to queries – for example SCRTY_ACC_GRP is the PeopleSoft Query Access Group Table.

As you can see, some of these tables are not well documented. So if you have more info on any of them, please let me know in the comments.

Leave a Reply

Your email address will not be published. Required fields are marked *