PeopleSoft Query – Which Tables are Involved?

by Apurva T. on May 2, 2014

in PeopleSoft Administration

PeopleSoft Query is a great way to retrieve information from PeopleSoft without having to write the complex SQL queries. However, as a PeopleSoft admin – there may be situations when you need to seek information from the PeopleSoft Query tables. For example, find out the private queries of a PeopleSoft user; Convert all private queries to public queries; when was a particular PeopleSoft query last updated etc.

As a PeopleSoft user, you might be aware of the fact that the actual SQL that is generated for a query isn’t actually stored in text format but is generated on the basis of information stored in a number of tables, which are categorized under the name PeopleTools tables.

Complete List of PeopleSoft Query Tables

One of the best and easy way to find the PeopleSoft Query tables is to know the fact that the PeopleSoft Query tables follow  a naming convention, which is table names start with PSQRY. The SQL that you see is generated on the basis of all these tables rather than being stored in one column in one table.

The full list of tables involved with PeopleSoft Query does change every now and the list below is good as of PeopleTools 8.53. Some of the important PeopleSoft Query tables have been explained to begin with.

PSQRYACCLSTRECS

PSQRYBIND – stores all run-time prompt data

PSQRYBINDLANG

PSQRYCRITERIA – stores all criteria expressions in code format

PSQRYDEFN – stores high-level query definitions with version numbers.

PSQRYDEFNLANG – Non-English detail is stored in it as well as in PSQRYHEADLANG

PSQRYHEADLANG – Non-English detail is stored in it as well as in PSQRYDEFNLANG

Also Read:  Get Count of All PeopleSoft Tables with one SQL

PSQRYFIELD – stores all fields used in all aspects of query operation

PSQRYDEL

PSQRYEXECLOG

PSQRYEXPR – stores the text associated with each criteria expression

PSQRYFAVORITES

PSQRYFIELD – stores all fields used in all aspects of query operation

PSQRYFIELDLANG

PSQRYFLAGS

PSQRYLINK – stores the relationships to child queries

PSQRYPREFS

PSQRYRECORD – stores all records used in all aspects of query creation

PSQRYSELECT – stores all SELECT requirements by select type, i.e. union, subselect, join,….

PSQRYSTATS

PSQRYTRANS

PSQRYXFORM

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: