PS Query & SQL

 View Only

  • 1.  What record/table would I query?

    Posted 06-11-2025 07:35 AM

    So, I'm looking to find examples of queries that use aggregates.  I know how to query the SQL for the stuff below.  But what table is the SQL for query stored in?  Or maybe query SQL is created on the fly?  I think I have all of the PSQRY tables, but I can't figure out how to find all of my queries that has COUNT in it or SUM or whatever else I have.  Looking to find examples for a new employee. 

    SQL behind a view:  PSSQLTEXTDEFN
    SQL behind cobol:  SQLSTMT_TBL
    SQL for Equation Callable SQL:  EQTN_SQL_CHUNKS

    Thanks!



    ------------------------------
    Dana Pawlowicz
    Business Systems Analyst Sr - ERP
    University of Cincinnati
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------
    Alliance 2026 Registration is Open!


  • 2.  RE: What record/table would I query?

    Posted 06-11-2025 08:29 AM
    The SQL is created on the fly.

    Try looking in PSQRYEXPR.  

    David Ehrlich | Senior Business System Analyst - Admissions, Student Records, Data & Reporting

    Duke University | Student Information Services & System (SISS)

    david.ehrlich@duke.edu | 919-684-1270

    www.sissoffice.duke.edu





    Original Message
    Alliance 2026 Registration is Open!


  • 3.  RE: What record/table would I query?

    Posted 06-11-2025 08:35 AM

    Thanks, David!  Yeah, I thought was created on the fly.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 4.  RE: What record/table would I query?

    Posted 06-11-2025 08:30 AM
    Hey Dana!

    This will kind of get you there.

    On the PSQRYFIELD table, there are two fields that I would use for this.

    TTLTYPE will give you a number that represents a change to the type of field based on which aggregate it is when you assign the aggregate to the field property.

    image.png


    Then for your Expressions that are aggregates, you can use the AGGREGATEFUNC = 'Y' in the sable record.

    Best,

    Jeffrie
    --
    Jeffrie Brooks | BUSINESS SYSTEM ANALYST
    UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
    734-647-8763 | jedobr@umich.edu



    Original Message
    Alliance 2026 Registration is Open!


  • 5.  RE: What record/table would I query?

    Posted 06-11-2025 08:34 AM

    Thanks, Jeffrie!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: What record/table would I query?

    Posted 06-12-2025 12:06 PM

    Hi Dana,

    Jeffrie's suggestions are likely to get you what you need for this. However, I just wanted to mention a presentation I gave in 2022 that talks about:

    • PeopleTools tables and what they do
    • Query tables and what you can do with them and where they fall short
    • An app engine I created that exports SQL from all your PS Queries to a custom table, so that you can search the full SQL text for any reason.

    It's called "Query your query quarry quickly: Fun with PeopleSoft metadata tables":  https://www.heug.org/viewdocument/?DocumentKey=34c6784d-27ac-48b3-815e-dd1c58b71369

    Like I said, you already have the answer to your current question, but maybe this presentation will help you with the next one!



    ------------------------------
    Scott Douglas
    Sr. Business Systems Analyst, Student Information Systems
    Azusa Pacific University
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 7.  RE: What record/table would I query?

    Posted 06-12-2025 12:36 PM

    Thanks!  I don't have this one, I'm going to download it and make sure it's in the resource center if it isn't already.


    Thanks, Scott!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 8.  RE: What record/table would I query?

    Posted 06-12-2025 12:39 PM

    It is in the resource center.  Dan put it in there.  I'm downloading it now.  Thanks!


    Just a reminder to others to check out the resource center.  And let us know if you have a presentation that we don't.  There is a folder for each conference.  Just to note, we have to wait 1.5 years after a conference before we can add any content so you won't see a folder for Alliance 2025.

     

    But open the folders like this (ignore the numbers)

     

    Resource Center > PS Query > Presentations > ... then you'll see a folder for the different conferences/years.

     

     




    Original Message
    Alliance 2026 Registration is Open!


Related Content