| Finding PS Queries | KEVIN RESCHENBERG
03-15-2006 |
In last week's post
I touched on some of the issues faced by those of
us who support users of PeopleSoft Query. At times you
might need to find the query that
produced a particular spreadsheet, or find an
existing query that could be the solution for a new
requirement. How can this be done?
Information about the various records, fields,
criteria, etc. are stored in tables whose names
begin with "PSQRY." The PSQRYFIELD table is very
useful. With a fairly simple report, where you
can guess the field names easily, you could try
something similar to this:
SELECT A.OPRID, A.QRYNAME
FROM PSQRYDEFN A
WHERE EXISTS (SELECT 1
FROM PSQRYFIELD
WHERE OPRID = A.OPRID
AND QRYNAME = A.QRYNAME
AND COLUMNNUM = 1
AND FIELDNAME = 'EMPLID')
AND EXISTS (SELECT 1
FROM PSQRYFIELD
WHERE OPRID = A.OPRID
AND QRYNAME = A.QRYNAME
AND COLUMNNUM = 2
AND FIELDNAME = 'NAME')
AND EXISTS ...
It is not guaranteed that the
column numbers will match what you see on the
report, though. If you don't get a hit on the
first try, remove the AND COLUMNNUM= conditions.
Don't try to list every field. Just
list a few of them, run the SQL, and see what you
get. If there are too many hits, add more of
the fields to narrow the search. If you get fewer hits
than expected, remove some of the more questionable
fields.
The PSQRYFIELD table also stores the record
(table or view) name and other information
that could be used in this search. There are also
several other PSQRY tables that could be used.
If you have rolled out PeopleSoft Query to
many users, you could have thousands of queries
out there. Using the definition tables could help
you manage them, rescue lost or orphaned queries (when someone
leaves the company, for example), and avoid
reinventing the wheel with every new
query request.
|
