PS Query & SQL

 View Only

  • 1.  PS query and date fun

    Posted 05-12-2025 05:59 PM

    Does anyone have any suggestions on SQL tuning or getting around PS query messing with dates?   In the past few months, I have been dealing with queries taking longer to run than they used to take.  Many times, updating timestamp fields to date fields has fixed the issue, but I have to mess with changing the format of a field multiple times to do that.   Below is an example snippet of a very ineloquent update.  I am all ears on any suggestions when dealing with these issues, thanks!

    Expression in PS query:

    CASE
    WHEN  TO_DATE(B.POSTED_DATE,'YYYY-MM-DD') BETWEEN TO_DATE(J.SRVC_IND_DTTM,'YYYY-MM-DD') AND  TO_DATE(J.AUDIT_STAMP,'YYYY-MM-DD') THEN...

    The SQL tab on PS query:

    CASE
    WHEN  TO_DATE( TO_CHAR(B.POSTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN TO_DATE( TO_CHAR(CAST((J.SRVC_IND_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') AND  TO_DATE( TO_CHAR(CAST((J.AUDIT_STAMP) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') THEN

    CS system details if helpful  

    PI Version:   9.2.033    

    Tools Version: 8.60.20



    ------------------------------
    Ross Nolan
    Data and Reporting Analysis
    University of California, Berkeley
    rvnolan@berkeley.edu
    ------------------------------
    Alliance 2026 Registration is Open!


  • 2.  RE: PS query and date fun

    Posted 05-12-2025 07:51 PM

    Hi! 

    PS Query and Date/Timestamp fields are basically bad news.  What you're doing is what you have to do.  The one suggestion I have is if you are using the same field in a criteria and as a field, use different expressions for each. 

    Vic



    ------------------------------
    Vic Goldberg
    University of Colorado
    goldbergvictor@gmail.com
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 3.  RE: PS query and date fun

    Posted 05-12-2025 09:02 PM
    Hey Ross,

    If you happen to find yourself with only a single instance of any date or date time stamp field, you can remove the alias from the field, which tricks PS into removing some of that extra stuff.

    So...

    B.POSTED_DATE becomes POSTED_DATE J.SRVC_IND_DTTM becomes SRVC_IND_DTTM
    J.AUDIT_STAMP becomes AUDIT_STAMP

    As you'd expect, this breaks down if you have more than a single instance of any field, since the query won't be able to tell which is which. So it can be a bit limited in application.

    I'm not sure that this will solve your issue, but hopefully it adds a bit of direction!

    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!


  • 4.  RE: PS query and date fun

    Posted 05-13-2025 01:23 AM

    Hi Ross,

    This could be something for DB Admins to look at with respect to tunig. On an easier way to write expressions, you could use Meta-SQLs. To turn Datetimes around to Dates use %Dateout.

    You could find the Meta-SQL references here, https://docs.oracle.com/cd/G23954_01/pt862pbr1/eng/pt/tpcl/Meta-SQLReference-073bd4.html?pli=ul_d1863e63_tpcl

    Where can you use them can be found here, https://docs.oracle.com/cd/G23954_01/pt862pbr1/eng/pt/tpcl/Meta-SQLPlacementConsiderations-073bd5.html?pli=ul_d1863e63_tpcl

    New Expression can be,

    CASE
WHEN  %dateout(B.POSTED_DATE) BETWEEN %dateout(J.SRVC_IND_DTTM) AND  %dateout(J.AUDIT_STAMP) THEN...

    Hope these help

    Regards,

    TB



    ------------------------------
    TUSHAR BABU
    BA
    The Australian National University
    ------------------------------

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

    Original Message
    Alliance 2026 Registration is Open!


  • 5.  RE: PS query and date fun

    Posted 05-13-2025 04:53 AM

    If you do not need the full timestamp could you use TRUNC  (although may just be an Oracle thing).

    example:

    select systimestamp, trunc(systimestamp) as just_date
    from dual



    ------------------------------
    Daron Wild
    Senior PeopleSoft Developer
    University of Cambridge
    ------------------------------

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

    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: PS query and date fun

    Posted 05-13-2025 11:20 AM

    Good morning Ross,

    If you have access to it, here is a link to my Alliance24 (Phoenix) presentation on DateTime and Query:

    https://www.heug.org/communities/community-home/librarydocuments/viewdocument?DocumentKey=f0e4a3a2-4891-4498-b5d4-9019b68a146a

    Also, I agree with Vic. You're doing it right already. 

    Find a date format that can be common to all of the fields you need.

    Write each Date or Date/Time field as an expression, using SQL operators to manipulate the native format into the chosen common one.

    Then, use those expressions in place of the native fields in your criteria, etc.

    TRUNC may work in some circumstances. As will the operators I have listed in the table in my session. 

    Another you may try is SUBSTRING. Sometimes that is an efficient way to select the part of a DateTime field you need.



    ------------------------------
    Scott Frey
    Senior Data Analyst - Human Resource Information Systems
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 7.  RE: PS query and date fun

    Posted 05-13-2025 01:35 PM

    Thank you all for the helpful suggestions!  I will test the different options and see which ones work best for this scenario, as well as save some of these ideas, as I bet they will be helpful later as well.



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

    Original Message
    Alliance 2026 Registration is Open!


Related Content