PeopleSoft Query – Which Tables are Involved?
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
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
