Search code examples
sqlpeoplesoftpeoplecode

Knowing how many records inside a query in a project

So I have this homework wherein if a PeopleSoft project has a query in it, I try to see how many records are inside this query for performance reason.

I tried going through meta table but I can't seem to find any record that stores this information, or might have miss it.

I have tried some meta table but the closes I got is PSRECDEFN which only has a field for field count.

Does anyone have any experience or idea how to do such task?

Appreciate all the help.

Thanks!

Solution

  • Every item added to an Application Designer project is stored in PSPROJECTITEM. The OBJECTTTYPE indicates what kind of definition it refers to. OBJECTTYPE 10 is for Query definitions.

    The records used by a query are stored in PSQRYRECORD. If you need information about fields, use PSQRYFIELD (also has record names).

    Joining these 2 tables will meet your requirement:

    SELECT A.PROJECTNAME, A.OBJECTVALUE1 "QUERY", COUNT( DISTINCT B.RECNAME) "RECORDS IN QUERY"
FROM PSPROJECTITEM A, PSQRYRECORD B
WHERE A.PROJECTNAME = :1
    AND A.OBJECTTYPE = 10
    AND B.QRYNAME = A.OBJECTVALUE1
GROUP BY  A.PROJECTNAME, A.OBJECTVALUE1;

    There is clear and categorized list of PeopleTools (meta) tables available at digitaleagle (no affiliation).