PS Query & SQL

 View Only
Expand all | Collapse all

The Most Underutilized PSQuery Features – What's Your Hidden Gem?

  • 1.  The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 08:57 AM

    PSQuery is a powerful tool, but many users only scratch the surface of what it can do. Features like expressions, drilling URLs, and subqueries often go unnoticed. What's a PSQuery feature you think more people should be using? Share your experience and any tips you have!



    ------------------------------
    Daniel Labrecque
    Senior 2 Business Systems Analyst/Functional Architect
    University of Nevada, Las Vegas
    ------------------------------

    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: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 09:03 AM

    I don't know if I'd call this a gem, but I have found that when doing a calculation and I'm having weird rounding issues, if I change it from Number to Signed Number in the expression, that fixes it.  So if you are having rounding issues, I recommend changing it to signed number.

     




    Alliance 2026 Registration is Open!


  • 3.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 11:18 AM
    Edited by Elizabeth O'Connor 02-21-2025 11:20 AM

    So many great suggestions and ideas in here! This is really great input and so happy to be involved in this group.

    For me, drilling URLs are one of my favorite go-to's in our office. We have two campuses that are vastly different in their tuition calc setups, one a medical campus to boot, and so we have a lot of cross-checking and correcting we need to do. I am in SF/Bursar and we do a lot of tuition calc/error reporting, and I have drilling URL components added in nearly all of my queries for making quick updates to equation variables or jumping out to do a student tuition calc. 

    I also include component drilling URLs when testing queries so I can quickly jump out and validate my data.

    One of the most underutilized for me is free form URL. We are often taking students from production to our test environments to test solutions to academic record issues. Free form URL allows me to build in a hyperlink that can take a student from production and jump immediately into test in order to work on corrections. 

    You can take your usual component drilling URL and adjust it in free form to allow it to jump into a different environment yet still carry over your output fields:

    Component URL for View Customer Accounts:

    'psp///c/MAINTAIN_CUSTOMERS.ACCOUNT_VW.GBL?Page=ACCOUNT_SF&Action=U&BUSINESS_UNIT=CUDEN&EMPLID=%A.EMPLID%:A.EMPLID'

    All the information you need is in your browser URL of the destination environment:

    '/f/[https://icsstg.qa.cu.edu/psp/icsstg/EMPLOYEE/HRMS/c/MAINTAIN_CUSTOMERS.ACCOUNT_VW.GBL?Page=ACCOUNT_SF&Action=U&BUSINESS_UNIT=CUDEN&EMPLID=%B.EMPLID%]:STG - VIEW CUST ACCTS'

    You can also take information from PeopleSoft Campus Solutions to PeopleSoft HCM as another example.

    Thanks!

    Elizabeth O'Connor



    ------------------------------
    Elizabeth O'Connor
    System Operations Manager
    University of Colorado
    ------------------------------

    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!


  • 4.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 09:10 AM

    Hey, Daniel, good question!

    So many different funtionalities, so many ways to get it right and/or wrong! I think that this is one of the reasons people might shy away from incorporating more involved ps query features. This is what I often hear from users for whom I write queries. They want something that is, first and foremost, correct and can be validated. However, they also want to "manipulate" the extract in Excel. I get it, that is totally sensible, but I would ask why? How often do you run the query? How do you use the results that you get from it? Do you need to share this information with others? Is there anything that we can leverage within the features of ps query that can make your life easier? Here is an example:

    User needs to pull a list of all classes in a term and be able to figure out which classes are under or over capacity. They need to track this information multiple times at the beginning of each semester. They use a simple query that pulls class enrollment data for each section. This includes total (ENRL_TOT) and capacity enrollment (ENRL_CAP). They open the results in Excel and create a new column with a formula that calculates the capacity percentage. So, they have a good solution and they could probably continue using this method (perhaps create a short macro and a template where they can upload that data so as to bypass the creation of the formula each time). However, this could very easily be accounted for in the ps query itself, saving them time and effort. 

    To answer, your original question, I think Aggregated Calculations might be underutilized by some PS users because they are more comfortable using tools like Excel or they are not knowledgeable (yet) about the power of such calculations. 

    Best,

    Anna 



    ------------------------------
    HEUG Community of Practice
    Reporting, Analytics, and Data Governance Subcommittee

    Anna Kourouniotis MA
    Database Analyst II
    Duke University
    ------------------------------

    Alliance 2026 Registration is Open!


  • 5.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 09:21 AM

    Anna, I'm guilty of that.  Do you know how to do this in PS Query because I never could get it to work so I just do it in Excel.  I'm getting my run times for the batch jobs to calculate how long it ran and I want that result to be in hh:mm:ss format.


    So, in Excel it looks like this.  The pink is my calculation.  End Time – Begin Time.

     

     

    Full SQL for query:

     

    SELECT DISTINCT A.prcsinstance,
                    A.prcsjobseq,
                    A.prcstype,
                    A.prcsname,
                    A.jobnamesrc,
                    A.runcntlid,
                    To_char(Cast(( A.begindttm ) AS TIMESTAMP),
                    'YYYY-MM-DD-HH24.MI.SS.FF'),
                    To_char(Cast(( A.enddttm ) AS TIMESTAMP),
                    'YYYY-MM-DD-HH24.MI.SS.FF'),
                    A.runstatusdescr,
                    Max(C.message_parm)
    FROM   ((ps_pmn_prcslist A
             left outer join ps_message_log B
                          ON B.process_instance = A.prcsinstance
                             AND B.program_name = 'ROWPROC' )
            left outer join ps_message_logparm C
                         ON B.process_instance = C.process_instance
                            AND B.message_seq = C.message_seq )
    WHERE  ( A.prcsinstance BETWEEN :1 AND :2 )
    GROUP  BY A.prcsinstance,
              A.prcsjobseq,
              A.prcstype,
              A.prcsname,
              A.jobnamesrc,
              A.runcntlid,
              A.begindttm,
              A.enddttm,
              A.runstatusdescr
    ORDER  BY 1 

     

     




    Alliance 2026 Registration is Open!


  • 6.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 09:57 AM
    Hi Dana,

    For this, the alias on the table complicates things. For whatever reason having that in your expression creates some additional formatting logic in the SQL.

    E.g.  A.BEGINDTTM looks different in the SQL than begin just BEGINDTTM. The extra code that it creates makes it so it throws out errors, instead of just letting you make that calc! 

    To get around this, try using this expression in your Query to see if it works for you: SUBSTR(ENDDTTM - BEGINDTTM,12,8). You'll notice the missing "A.". 

    Caveat: this solution will only work if you only have a single table with those fields in it. Otherwise the alias is required to distinguish which record you are referencing. I am sure there is a more sophisticated way to get at that calc, but this has worked pretty well for me.

    Jeffrie

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



    Alliance 2026 Registration is Open!


  • 7.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 10:26 AM

    Thank you so much, Jeffrie!!!  That works!  I just need to move some stuff around.  😊  YEAH!

     

     




    Alliance 2026 Registration is Open!


  • 8.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 10:03 AM

    Hey Dana,

    I have never attempted this because it isn't something I have had to report on. Very good question though!

    Best,

    Anna 



    ------------------------------
    HEUG Community of Practice
    Reporting, Analytics, and Data Governance Subcommittee

    Anna Kourouniotis MA
    Database Analyst II
    Duke University
    ------------------------------

    Alliance 2026 Registration is Open!


  • 9.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 03:19 PM

    I've been using the following expression to report the amount of time a process runs, %DateTimeDiff(%DateTimeIn(C.BEGINDTTM),%DateTimeIn(C.ENDDTTM))

    Only problem is it returns the difference in minutes.



    ------------------------------
    Jeffrey Wal
    Manager of Financial Systems
    San Diego State University
    ------------------------------

    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!


  • 10.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 03:23 PM

    Jeffrey Brooks gave me this one and it works perfectly!

     

    I had to remove the A. and then also use his expression below.

     

     




    Alliance 2026 Registration is Open!


  • 11.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 03:37 PM
    Ohhh... that's nice! I'm going to borrow that too! Thank you!

    Jeffrey Wal
    Manager of Financial Systems Management and Analysis
    San Diego State University
    Controller's Office
    (619) 594-4865



    Alliance 2026 Registration is Open!


  • 12.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 09:28 AM
      |   view attached

    Here's a couple that I use all the time:

     

    Getting the SQL logic behind a view

    Records that end in "VW" are views, which are essentially SQL statements that are used like tables.  To get the SQL logic of what's behind the view, put the record: PSSQLTEXTDEFN in your query tree.  Then you can set SQLID = <the name of your view> and the result (in the field:  SQLTEXT) will be the SQL behind the view in the field.

     

     

    Cut and Paste ID's into your query

    See attached for directions I created for functional users to cut and paste ID's into an expression to get results.  This concept works with anything that can be put into a list.  These are the things I used it for recently:

    • Get ID's from a list of email addresses
    • Find students enrolled in any one of a list of 50 programs

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Attachment(s)

    Alliance 2026 Registration is Open!


  • 13.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 11:43 AM

    @Scott Cho Thanks for the lesson using PSSQLTEXTDEFN.  I just created a query using this table and have added it to my "toolbox."



    ------------------------------
    Daniel Labrecque
    Senior 2 Business Systems Analyst/Functional Architect
    University of Nevada, Las Vegas
    ------------------------------

    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!


  • 14.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 10:16 AM

    I love the Transformation option. We use it for one of our queries that feeds into Slate. They kept having to manually manipulate the report to remove the top line of the CSV file then load it. So I use an XSLT to automatically remove that top line. 



    ------------------------------
    Somaly Heang
    Senior Associate Director of Operations
    University of Massachusetts-Boston
    ------------------------------

    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!


  • 15.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-26-2025 11:15 AM

    Can you describe what you are referring to with the transformation option? I would love to know how to remove the first line of a query export.



    ------------------------------
    Christina Becan
    Business Systems Analyst
    Texas Christian University
    ------------------------------

    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!


  • 16.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 09:14 AM
      |   view attached

    Hi Christina 

    Here are my procedures for CSV transformation. Let me know if have any questions. 



    ------------------------------
    Somaly Heang
    Senior Associate Director of Operations
    University of Massachusetts-Boston
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------

    Attachment(s)

    pdf
    Creating Transformations.pdf   736 KB 1 version
    Alliance 2026 Registration is Open!


  • 17.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 09:32 AM

    for Transformations ... does anyone know the XSLT syntax to include a header row with column names? Use transformations for pipe separated (<xsl:variable name="separ">|</xsl:variable>)  output and have had requests for the header row.

    Thank you



    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania State University
    ------------------------------

    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!


  • 18.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 09:39 AM

    Mine includes a header row.  The yellow section is my headers.  I'll be honest, I basically copy & pasted and just updated from a presentation. 

     

     

    <?xml version="1.0" encoding="ISO-8859-1"?>

    <xsl:stylesheet version="1.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>

    <xsl:variable name="separ">,</xsl:variable>

    <xsl:variable name="delim">"</xsl:variable>

    <xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="utf-8"/>

    <xsl:template match="/query">

    <xsl:text>STUDENTID</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>FIRSTNAME</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>LASTNAME</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>EMAIL</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>TEMPLATETYPE</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>AWARDYEAR</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>FUNDCOSTNAME</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>FUNDCOSTAMOUNT</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>FUNDCOSTTERM</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>ALTEMAIL</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>PHONE</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>DEPENDENCY</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>PLUSBORROWING</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>EFC</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>PORTAL</xsl:text>

    <xsl:value-of select="$separ"/>

    <xsl:text>&#xA;</xsl:text>

    <xsl:for-each select="/query/row">

    <xsl:value-of select="C.CAMPUS_ID"/><xsl:value-of select="N.CAMPUS_ID"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="C.FIRST_NAME_SRCH"/><xsl:value-of select="N.FIRST_NAME_SRCH"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="C.LAST_NAME_SRCH"/><xsl:value-of select="N.LAST_NAME_SRCH"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="EMAIL"/><xsl:value-of select="EMAIL2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="TEMPLATETYPE"/><xsl:value-of select="TEMPLATETYPE2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="E.DESCRSHORT"/><xsl:value-of select="Q.DESCRSHORT"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="FUNDCOSTNAME"/><xsl:value-of select="FUNDCOSTNAME2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="FUNDCOSTAMOUNT"/><xsl:value-of select="FUNDCOSTAMOUNT2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="G.STRM"/><xsl:value-of select="R.STRM"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="ALTEMAIL"/><xsl:value-of select="ALTEMAIL2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="PHONE"/><xsl:value-of select="PHONE2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="I.DEPNDNCY_STAT"/><xsl:value-of select="U.DEPNDNCY_STAT"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="PLUSBORROWING"/><xsl:value-of select="PLUSBORROWING2"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="K.PRIMARY_EFC"/><xsl:value-of select="W.PRIMARY_EFC"/>

    <xsl:value-of select="$separ"/>

    <xsl:value-of select="L.VARIABLE_FLAG2"/><xsl:value-of select="X.VARIABLE_FLAG2"/>

    <xsl:value-of select="$separ"/>

    <xsl:text>&#xA;</xsl:text>

    </xsl:for-each>

    </xsl:template>

    </xsl:stylesheet>

     




    Alliance 2026 Registration is Open!


  • 19.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-27-2025 09:44 AM
    Thank you, Dana!!! Something I had been chasing for a while. Figured it was straight forward but I never found the correct syntax!!!!


    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University
    rtf12@psu.edu



    Alliance 2026 Registration is Open!


  • 20.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 11:15 AM

    One of my favorites is the decode expression.  The biggest timesaver I use it for is for one of our third party vendors.  They have defined their own codes for our various tuition plans (we have about 6 plans active at a time, 5 guaranteed rates and a variable plan).  Their codes are completely different than anything we would have stored in PeopleSoft and they change the codes at least every year, sometimes every semester.  We have a couple of different places that have a student's tuition plan, but the easiest is their student group.  I use decode to say if their student group is X, Y or Z then return "random code 1" if it is A, B or C then return "random code 2", etc....

     

    This vendor has around 200 students a semester so trying to update each one manually in excel is time consuming and has a lot of potential for error, especially considering the user who gets the data is not tech savvy and probably doesn't even know how to do find and replace.

     

     

    Rebekah Lopez

    Student Financial Service – Systems Analyst

    The Office of Budget and Finance

    The University of Texas at Dallas

    (972) 883-4031

    Office: SSB 2.300

     

     




    Alliance 2026 Registration is Open!


  • 21.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 11:40 AM

    In regards to the DECODE, I use one pretty often in my Pop Selects that are part of my JobSet.  This is the expression:

    DECODE(EMPLID = EMPLID, 'Y')

    I then make that a criteria equal to a prompt:

    The prompt is set up as follows:

    When you run the query, the prompt box comes up.  You will see at the bottom, Allow (Y/N).  If you check the box, the query will return rows.  If you leave it unchecked, it will not return any rows.

    Using this technique, I do not have to keep adding and removing run controls that use Pop Select in my JobSets.  The Yes/No Table is very handy for that.



    ------------------------------
    Daniel Labrecque
    Senior 2 Business Systems Analyst/Functional Architect
    University of Nevada, Las Vegas
    ------------------------------

    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!


  • 22.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-20-2025 11:47 AM

    Two that jump to my mind that have not been talked about yet.  Is using a checkbox prompt to change the output of a query.  For instance, change some fields to be blank to alter how items are aggregated or show more/less details.  In the below example change time fields from 24-hour to 12-hour display.  

    CASE WHEN :1 = 'Y' THEN

    CASE WHEN C.MEETING_TIME_START IS NOT NULL THEN SUBSTR(C.MEETING_TIME_START, 1,2) || ':' || SUBSTR(C.MEETING_TIME_START, 4,2)

    ELSE NULL END

    ELSE

    CASE WHEN C.MEETING_TIME_START IS NOT NULL THEN TO_CHAR(TO_TIMESTAMP(C.MEETING_TIME_START,'HH24.MI .SS.FF'),'HH12:MIAM')

    ELSE NULL END

    END

    (Y/N bind = :1 , 20 character field, 24hr first part  then 12hr)

    The other is using Meta-SQL in prompts or criteria to help the query be more dynamic.  In a prompt, use %Date as the default date value so it always starts with the current date. Setting query-specific row-level security by adding %OperatorId to an expression to pull in the OPRID for the query runner.



    ------------------------------
    Ross Nolan
    Data and Reporting Analysis
    University of California, Berkeley
    rvnolan@berkeley.edu
    ------------------------------

    Alliance 2026 Registration is Open!


  • 23.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 06:18 AM

    Ross,

    • 100% agree. Y/N prompts are a query writers best friend. They allow user input and then the level that the query writer can innovate its use based off your knowledge of SQL and your determination to gain more knowledge of SQL. Will be giving a presentation at Alliance - "Prompts, Stomps, everybody Romps" which includes this
    • Meta-SQL - use %currentdatein (as an expression) instead of SYSDATE. SYSDATE contains time so SYSDATE is not equal to a date field (there is not a time component) with a value of today. Using %currentdatein eliminates the need to manipulate SYSDATE.
    • %Date - another great thing is it does not need maintained on run controls. At run time it uses current date and like %currentdate in, no time element.

    Thanks,

    Bob



    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania State University
    ------------------------------

    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!


  • 24.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 09:21 AM

    I love the idea of not maintaining run controls by using the meta SQL.  However, it does not appear to be working for me.  Yesterday I set up a query with %Date in the Default value of a prompt.  I then set up a run control using that query (Population Update) and when I looked at Edit Prompts the correct date is there.  Today when I went to the run control, yesterday's date was still in the edit prompts of the run control.  When I run the process it is not getting the records from today. It is still using yesterday's date.  Did I do something incorrectly? 

     




    Alliance 2026 Registration is Open!


  • 25.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 09:43 AM
    Edited by Robert Fogarty 02-22-2025 08:35 AM

    Shareen,

    I know that it does not work in QBU and I needed to develop a work around.

    • First I set the Default Value (I used 1900-01-10, a date I wouldn't use)

    • Next add criteria for example,  A.EFFDT Eff Date <= decode(:1, date '1900-01-01' , %currentdatein, :1)



    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania State University
    ------------------------------

    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!


  • 26.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 09:45 AM

    Thanks!  Will see if I can get that to work!

     




    Alliance 2026 Registration is Open!


  • 27.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 07:40 AM

    Left Outer Joins - left outer joins do not adversely effect performance and can be "converted" to an standard join by simply adding criteria to the WHERE clause. For example:

    Standard joins can not be made into Outer Joins. I have had to totally re-write queries because I needed to add criteria in an outer joined sub-query with a field from a Record that was not outer joined. When in doubt ... use an Outer Join.

    My Alliance 2022 presentation - Outer Join Schmouter Join details the Left Outer Join

    Thanks,

    Bob



    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania State University
    ------------------------------

    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!


  • 28.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 01:00 PM

    The outer join functionality of PS Query has come a long way, and as Bob says, it is a real hassle to change an inner joined record to an outer join.  A caveat to add here, make sure you go in and change the "auto" effective date criteria to belong to the Join.  The effective date subquery is an exception to the normal "can't outer join on a subquery" error/check (both in PS Query and Oracle SQL).



    ------------------------------
    Scott Nishizaki
    Developer/Analyst
    Azusa Pacific University
    ------------------------------

    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!


  • 29.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 08:20 AM

    We have gained some utility by setting up concatenated expressions to perpetually derive the relevant values instead of having to reset prompts for scheduled reports periodically. I.e., for a refunding report used for checking activity for current or prior terms prior to a run, deriving the term based upon the usual, applicable months. One less piece of term setup as a To-Do for maintenance! I also like putting the derived value as a field on the report (if it's not part of the outputted values) so the analyst knows which term is being derived  by the expression without having to worry about it, just in case data goes sideways for some reason. This is a simplistic example however even small wins in efficiency add up over time.

    concatenated expression deriving a term



    ------------------------------
    Kimberley Wortkoetter CPA, PMP
    Sr. Business Analyst Lead
    Ohio State University
    ------------------------------

    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!


  • 30.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 09:45 AM

    I keep a private library of sample queries in PRD. Some of these queries are a time consuming pain to set up, but are a basis for many query requests (example: a paycheck union query that gives totals of hours and $ for pay earnings and pay oth earns...anytime someone wants info from paychecks on specific earnings, I just copy this and edit for appropriate earn codes). Others have complex SQL that I had written at some point and never want to have to rewrite or think about again. I've got a sample query that creates a report from a tree which we used to turn into a view, but I still like to have that as a reminder on how trees work (I actually passed this query onto one of our programmers at one point). The main advantage of these queries is I can copy them and write new queries faster than anyone else, because I've already got the base.

     

    A few years ago my supervisor asked how I could write queries so fast, and I confessed. Now, my co-workers know they can go into query admin and pick from my sample library. It's also been very nice to hand over to new query writers, so they can see how to do some of the things that they maybe didn't know were possible.

     

    Lynn

     




    Alliance 2026 Registration is Open!


  • 31.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 09:49 AM

    That's brilliant, Lynn!

     




    Alliance 2026 Registration is Open!


  • 32.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 10:12 AM

    Wow, there are some really great replies here already. Kind of wild that you can write queries for a decade and keep learning more tricks. Scott, the tip about the SQL behind views is great! I feel like you graduate to a new level of query writer when you understand how the SQL of a view impacts your results. Somaly, I need to look into the Transformation option, that sounds interesting, but I don't know the first thing about it. And Dan, I had to read your Y/N prompt post a couple of times to understand the impact. That's a wild idea actually.

    I would offer as my features, Unions and expressions with Case, Listagg, and concatenating with ||. Unions can be a little dangerous if you forget to update the criteria in the unions when you update the top level, but it allows you to return some very specific datasets without having to be super sophisticated with your logic. And when you need to output a field in a very exact way, for an integration for example, expressions with CASE, LISTAGG, and CONCAT ( || ) can be really powerful.

    Great info everybody! Cheers, John at Miami



    ------------------------------
    John Eggenton
    Senior Software Architect
    University of Miami
    ------------------------------

    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!


  • 33.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 12:09 PM

    I am excited to try some of these things that everyone is posting.  Some of these I have never considered. You folks have amazing ideas.



    ------------------------------
    Daniel Labrecque
    Senior 2 Business Systems Analyst/Functional Architect
    University of Nevada, Las Vegas
    ------------------------------

    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!


  • 34.  RE: The Most Underutilized PSQuery Features – What's Your Hidden Gem?

    Posted 02-21-2025 04:59 PM

    I've found it very useful to get an aggregate value from a subquery to get MIN / MAX or SUM or COUNT values in a criteria at the top level of the query and still return one row per student.  For instance, date of attendance.  Using STDNT_CAR_TERM at the top level, then set STRM = Subquery... and in that subquery is STDNT_CAR_TERM with criteria UNITS_TAKEN_PRGRSS > 0 and use STRM as a field and that field is set to MIN to get the first term the student is enrolled.  Join the TERM_TBL at the top level and you've got the start date of the first term where the student had enrollment.  Do it again and use MAX and now you've got the last term where the student has enrollment.

    You can get as involved in your subquery as you need to, adding whatever criteria is relevant, including creating complex expressions.  Maybe in the example above you don't want to include future terms in your dates of attendance, so instead you create an expression that's something like "case when UNITS_TAKEN_PRGSS > 0 AND TERM_BEGIN_DT <= SYSDATE then STRM end" then you use that expression as a field and set it to MAX and now you have the MAX term where there is enrollment but where the term begin date is not in the future.

    This also works well for effective dates, for instance getting the MAX effective date in a subquery where that effective date < the effective date at the top level of your query gives you the prior row.  Or getting the MAX effective date in a subquery where a student is not in a particular plan code (or list of plan codes or even ACAD_ORGs from ACAD_PLAN_OWNER) to return the effective date at the top level that's associated with the plan code a student had before entering a particular major or department.



    ------------------------------
    Jason Marshall
    Director - Enrollment Systems Operations
    Salem State University
    ------------------------------

    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!