PS Query & SQL

 View Only
Alliance 2026 | March 8-11, 2026 | Orlando, FL
Back to discussions
Expand all | Collapse all

What's your favorite expression, case statement, trick?

  • 1.  What's your favorite expression, case statement, trick?

    Posted 10-30-2025 11:54 AM

    What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

    Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

    SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

    Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

    So this solution is a bit unconventional but it has worked for me for a couple years now with our FA tables. 
    As you mentioned the amounts are multiplied by the number of rows being rolled up by the aggregate, so what we need to do is come up with the math that divides it by that same number again. After quite a bit of work, I came up with the following statement that seems to accomplish this for us. This example uses STDNT_AWARDS - which is important because the KEY fields play a big role in this.
    SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)
    So what is it doing? Its taking the SUM that you've been stuck with and DIVIDES that number by each row in the QUERY - - - "/COUNT(*)"
    That number actually ends up being too small. So we need to take that number and multiply it by the distinct key fields that are on the table that we want the SUM. - - - *COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER). The result should be what you are looking for.
    With different tables, you just replace the concatenated KEY fields.
    For STDNT_AWRD_DSB, the expression becomes: SUM(A.OFFER_BALANCE)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER || A.DISBURSEMENT_ID)/COUNT(*)
    For STDNT_CAR_TERM it could look like SUM(A.UNT_TAKEN_PRGRSS)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.ACAD_CAREER || A.STRM)/COUNT(*)

    It has worked really well as a work-around for me, with these tables and in our system. I would strongly recommend validating the data when you run it, especially if you are trying to apply this solution to other tables.


    ------------------------------
    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's your favorite expression, case statement, trick?

    Posted 10-30-2025 12:03 PM

    My favorite is the sly way you can add a list of EMPLID's from an expression.

     

    What you do is manually add a test like this and finish it off with the statement:  AND '1'

     

    A.EMPLID IN ('12345', '12354', '45678')

    AND '1'

     

    Then you add it to your criteria and make it equal to 1:

     

    This "tricks" the system into thinking this is two lines of criteria:

     

    SELECT A.EMPLID, A.AID_YEAR
      FROM PS_STDNT_AID_ATRBT A
      WHERE (  A.EMPLID IN ('12345', '12354', '45678')
    AND '1'     = '1')

     

    This trick has saved me from having to enter 200 EMPLID's for a query and instead I can grab them from a text editor/spreadsheet and get what I want.

     

     

     

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 3.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 12:16 PM

    I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!



    ------------------------------
    Timothy Krug
    Consulting Manager
    Huron Consulting Group
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 4.  RE: What's your favorite expression, case statement, trick?

    Posted 30 days ago

    I use this all the time and have screamed it from the rooftops at my university, but I learned it from Robert Fogarty at Alliance a couple years ago. It would have been such a great tool when implementing for testing.

     

    Morgan Andres
    Senior Business Systems Analyst

    Student Information Services and Systems (SISS)

    Duke University

    1121 West Main St., Suite 2200

    Durham, NC 27701

    E-mail: morgan.andres@duke.edu

     




    Original Message
    Alliance 2026 Registration is Open!


  • 5.  RE: What's your favorite expression, case statement, trick?

    Posted 30 days ago
    The complimentary expression for not in list is

    <>  ALL('10000001','10000002','1000003')

    Bob
    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University



    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

    So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?



    ------------------------------
    Kendall Vance
    IT Technical Associate for Financial Aid Reporting
    Northern Illinois 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!


  • 7.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago
      |   view attached

    It is. But the amount of values you can pass is really small due to size you can make the prompt.

     

    This presentation had a different way to do the "list of ids" and they have slides on doing it with a prompt.  Starts on slide 15.

     

    But, maybe with multiple optional prompts you can get a decent size list. 

     

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 8.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    Bob Fogarty does make a slight mention of it as in the thread.  I think it is fantastic that you can do that.  His example was IDs, but there is no reason it can't be other fields as needed.



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


  • 9.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    Thanks! This session shows the one that I called "Manual Unpivot", they say "Flatten" and use Max() rather than Sum().   Good chance this is where I stole the idea,.... looks familiar.



    ------------------------------
    Kendall Vance
    IT Technical Associate for Financial Aid Reporting
    Northern Illinois 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!


  • 10.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago
    Kendall,
    The only way that I have been able to add a list through a prompt is using INSTR as detailed in post #44 of this thread
    Bob
    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University



    Original Message
    Alliance 2026 Registration is Open!


  • 11.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago

    Good morning Bob & Kendall,

    I wasn't able to locate Post #44 in my blizzard of emails, so I apologize if this is redundant.

    I use the following INSTR expression and criteria. As previously mentioned, this method is limited by the field width of PSQuery prompts, and by the character-count of whatever field you're using in the prompt. Prompt values are comma-space separated when entered, e.g. 

    XXXXX1, XXXXX2, XXXXX3, XXXXX4, etc.

    In this example, I'm prompting for EmplID. Univ. of Colorado uses 6-digit EmplIDs, and I think I can search five at a time. I don't use this option very often. My customers are usually FAs. But it does have its applications.

    (CASE

    WHEN REGEXP_INSTR(:1, A.EMPLID) > 0

    THEN A.EMPLID

    END)

    =

    A.EMPLID



    ------------------------------
    Scott Frey
    Senior Data Analyst - Human Resource Information Systems
    University of Colorado System
    ------------------------------

    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!


  • 12.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago

    We have several collection queries that require multiple SID prompts, and so we have this configured for our use:

    First prompt is required but all others are marked optional:

    We pull SID lists from excel columns which adds the space, so in this case we use 25 SID list max.



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

    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!


  • 13.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago

    I think the posts may renumber themselves if one of us adds a reply to a "higher up" post.

    We've utilized this prompt method before, and agree that INSTR(:1, A.EMPLID) > 0 is the only way we've found to allow this.  Calling this a "limited number" is a bit unfair since a PS Text prompt can handle 4,000 characters (this is the PS universal limit for a Char vs CLOB)  So even if you're doing 'EMPLID1', 'EMPLID2',... you could get at least 266 IDs (assuming you use all 11 characters in EMPLID).

    @Scott Frey, I'm wondering why you use REGEXP_INSTR(...) rather than INSTR(...).  Is there an advantage to this?



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    Developer/Analyst
    Azusa Pacific University
    ------------------------------

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

    Original Message
    Alliance 2026 Registration is Open!


  • 14.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago

    Thanks for providing the character limit for the prompts Scott. I'm always learning from you.

     

    To be honest, I don't know if there is an advantage to the REGEXP_

    I haven't used this expression in a few years.

    I just remembered having it in my "bag of dirty tricks and hacks" and thought I'd throw it out here in case anyone can benefit from it.

     

    ...it's probably something I learned at Alliance.

     

     

    Scott Frey

    Senior Data Analyst-HRIS, Employee Services

    University of Colorado

    1800 Grant Street, Suite 400

    Denver, CO  80203

    t  303 860 4200

    Scott.Frey@cu.edu
    www.cu.edu

    A black text on a white background AI-generated content may be incorrect.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 15.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    I agree - this ANY solution makes much more sense than the 1 = 1 workaround.



    ------------------------------
    Kevin Shalla
    Director of Technology
    University of Chicago
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 16.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

    ARTP - A&R Temp Group

    I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  



    ------------------------------
    Lee Raubolt
    Associate Director of Admissions and Records
    Truckee Meadows Community College
    ------------------------------

    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!


  • 17.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    Oh, I like this solution a lot!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 18.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    I tend to do this with student groups too – if someone has a list of IDs in a file you can easily load them into a group and then query off that group.

    And being able to then delete the student group afterwards will make that a perfect solution

     

    Phil

     

     

     

    University of Derby

     

     

    Phil Sugden

     

    Head of Registry Systems Enhancement
    The Registry

    01332 591053
    p.sugden@derby.ac.uk

     

     

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 19.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago

    For those utilizing Student Groups, may I make an alternate suggestion?  Student Blocks (assuming you have access).  A student block can be populated using an external file but would be totally invisible to anyone/everyone else.  You could then query the ID's in that student block similarly to how you would for those in a student group.

    Example Student Block Setup

     



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 20.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago

    Scott - are you saying that the advantage of using student blocks over student groups is that student blocks cannot be viewed by others? I can see student blocks created by others.



    ------------------------------
    Kevin Shalla
    Director of Technology
    University of Chicago
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 21.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    Thanks for asking Kevin,  I meant that the student block doesn't have any "down the line" ramifications.  It doesn't show up on any screen that isn't student block, so you'd have to be looking for it.  And you wouldn't need to do any extra effort to inactive or remove it like you would a student group.  



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 22.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 12:20 PM

    One of my go-to tricks when building a query for a Pop Select in a run control is using:

    

    

    

    

    

    

    

    DECODE(A.EMPLID, A.EMPLID, 'Y')

    I tie this to a simple Y/N prompt. When the prompt box is checked (Y), the query returns rows; when it's left unchecked (N), it returns nothing.

    It's a handy way to toggle a run control on or off, especially when it's part of a JobSet, without having to constantly add or remove that run control throughout the aid year.



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


  • 23.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 10:21 AM

    One additional note:

    This expression works great in PSQuery, but the number of listed values is 1,000 per expression. However, I haven't found a limit on the number of such expressions that can be used in a single query. I've successfully used 26. All but the first expression was added in criteria as an <OR>, and then all of the expression criteria rows were grouped.



    ------------------------------
    Scott Frey
    Senior Data Analyst - Human Resource Information Systems
    University of Colorado System
    ------------------------------

    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!


  • 24.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 01:25 PM

    Scott, are you saying you did

     

    1000 emplids

    OR

    1000 emplids

    OR

    1000 emplids

     

    .... 26 times?   NICE!!!!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 25.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 01:36 PM

    YES!

     

    The criteria looked like:

     

    (AND (A.EMPLID IN (list of 1,000 EmplIDs) and 1 = 1

    OR (A.EMPLID IN (list of next 1,000 EmplIDs) and 1 = 1

    OR (A.EMPLID IN (list of next 1,000 EmplIDs) and 1 = 1

    OR (A.EMPLID IN (list of next 1,000 EmplIDs) and 1 = 1

    Etc, etc.  )

     

    Twenty-six expressions. It's a very klunky way to do it. But it will work in PSQuery.

    Writing this as a single IN LIST expression in a SQL script and running it in a tool like SQLTools or TOAD is a lot more efficient... if you're the only one that needs to run the query. Or if your customers can run a SQL script themselves. My customers usually can't.

     

    Scott Frey

    Senior Data Analyst-HRIS, Employee Services

    University of Colorado

    1800 Grant Street, Suite 400

    Denver, CO  80203

    t  303 860 4200

    Scott.Frey@cu.edu
    www.cu.edu

    A black text on a white background AI-generated content may be incorrect.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 26.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 12:17 PM

    Simple but beautiful: LISTAGG(DISTINCT...)

    Mic drop.



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

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

    Original Message
    Alliance 2026 Registration is Open!


  • 27.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 03:06 PM

    With the added beauty of ON OVERFLOW TRUNCATE '...' WITH COUNT  never run in to the issue where your LISTAGG goes over 4,000 characters again.



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 28.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 12:39 PM

    I use this one fairly often when trying to list all the XXXXX's a student has (such as checklists, student groups, srvc inds, etc). 

    regexp_replace(LISTAGG(DISTINCT((R.CHECKLIST_CD)), ', ') within group (order by R.CHECKLIST_STATUS), '([^' ||chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')



    ------------------------------
    Alex White CBAP
    Senior Business Analyst
    University of Wisconsin - Madison
    ------------------------------

    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!


  • 29.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 05:10 PM
    What a great idea to share these!

    Okay... I don't really like writing (or reading) subqueries in PSQuery. They are cumbersome to build, modify... and even navigate when looking at a query, so I try to avoid them when I can. Also for whatever reason, whenever I write a query against the record, STDNT_FA_TERM that also involves subqueries, it runs super-slowly.

    In my experience, there are primarily two functional reasons to use a subquery:
    1. To limit the number of outputted rows based on a MAX() or MIN() value from the subquery.
    2. To exclude a sub-population of rows based on some logic that cannot be written in the top-level of the query.
    I have funky work-arounds for both of these scenarios, but only 1. involves an expression, so I will focus on that.

    Example: I want to find the number of credits students take during their final term. Querying the record, STDNT_CAR_TERM, I could return EMPLID, and UNT_TAKEN_PRGRSS and create a subquery, where STRM = MAX STRM from a subquery, with all of the relevant criteria from the top level duplicated:

    image.png
    Top level > Subquery fields/criteria

    In this example, there aren't many duplicated criteria, just to keep it simple, but in many use cases, you could have dozens of lines of logic to replicate - which isn't fun, and can lead to errors.

    So instead of writing the query this way, I will often use a combo of aggregates + concatenation + substr() to achieve the same results (mostly):
    image.png
    By taking the MAX of STRM and appending the units taken to it, you get rid of the need to use a subquery to determine that value. Then you wrap the whole thing in a SUBSTR() to extract the parts that you want to keep.

    SUBSTR(MAX(A.STRM || A.UNT_TAKEN_PRGRSS),1,4)
    SUBSTR(MAX(A.STRM || A.UNT_TAKEN_PRGRSS),5,3)

    A couple of things to note: 1) You will need to select "Aggregate Function" on each expression to get it to work. 2) Remember, "(mostly)" from before? Well, this  method does NOT actually remove the rows like a subquery would, it just suppresses them. So use caution if you are also using SUM() or COUNT() in your query.

    There may be times when a subquery still makes more sense, but I find myself using this method more often than not.

    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!


  • 30.  RE: What's your favorite expression, case statement, trick?

    Posted 10-30-2025 06:49 PM
    Dude.  You blow my mind all the time.   If you ever do a query presentation or webinar... I'm there!



    Original Message
    Alliance 2026 Registration is Open!


  • 31.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 03:03 PM

    I use NTH_VALUE for this purpose.  So for your example I would do something like

    NTH_VALUE(A.UNT_TAKEN_PRGRSS, 1) OVER (PARTITION BY A.EMPLID ORDER BY A.STRM DESC)



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 32.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 04:05 PM
    That makes so much sense! I was late to the window function game, so I never considered that before (but certainly will now!). Thanks Scott :)

    Question - since we are on the topic: Do you know of a way to use the output from an NTH_VALUE in your criteria? For example, if we wanted to pull ACAD_CAREER instead of UNT_TAKEN_PRGRSS, and then join that to a separate record, is that possible?

    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!


  • 33.  RE: What's your favorite expression, case statement, trick?

    Posted 11-03-2025 02:36 PM

    While you can't directly put a partition in to a criteria, it looks like you could probably get away with this as a subquery.  The below Query SQL did return the correct career information for a sample student.

    SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG
  FROM PS_ACAD_PROG A
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.ACAD_CAREER = (SELECT DISTINCT NTH_VALUE( B.ACAD_CAREER, 1) OVER (PARTITION BY  B.EMPLID ORDER BY  B.STRM DESC)
  FROM PS_STDNT_CAR_TERM B
  WHERE A.EMPLID = B.EMPLID)


    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 34.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 08:17 AM
    My favorite trick is combining EFFDT and EFFSEQ into one value (type Datetime) that still results in an indexed read ... I use it with subqueries. It is the only way I know to solve certain questions like "what is the preceding row?".

     
        TO_TIMESTAMP(B.EFFDT) + B.EFFSEQ/1440

     The value displays as a date, 10/31/2025, yet the actual value used is 10/31/2025 01 (EFFSEQ = 01), 10/31/2025 02 (EFFSEQ = 02) ...
    1440 is arbiitrary but convenient, it is the number of minutes in a day and results in a number that matches the EFFSEQ appending the date.

    Boo!!!
    Bob--
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University



    Original Message
    Alliance 2026 Registration is Open!


  • 35.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 08:56 AM

     

    I join to setup tables to pull extra rows and then use setup values as variables to create sub-totals, totals, or any other grouping. With aggregate expressions in columns.

     

    Table ACAD_CAR_TBL is a variable, creating a second row for a total row

     

    (Case

    When I.ACAD_CAREER = 'UG' Then DECODE(A.ADMIT_TYPE, 'FYR', ' First Year', 'TRN', ' Transfer', 'FYT', ' Transfer')

    When I.ACAD_CAREER = 'GR' Then 'Total UG'

    End)

     

    This query report has two groupings. Sections, and then Groupings within each section.

    Table TERM_TBL values are used to create an applicant row for each section. Some have multiple values due to Groupings (separate expression) within the Section.

     

    (Case

    When G.STRM In ('1002','1022') Then 'Total'

    When G.STRM = '1102' Then 'Round'

    When G.STRM = '1104' Then 'Home Residence'

    When G.STRM In ('1107','1117','1127') Then 'Socio Eco'

    When G.STRM = '1134' Then 'Appl Type'

    End)

     

    Here is query output, which is used in a BI Publisher PDF report. Some Sections are in unions, due to different aggregates in the same column (counts, percentages, and averages,). In this report that are 12 columns. Apply, Admit, Net Deposit, Enrolled. With three years under each, point-in-time comparison.

     

    Section Heading

    Grouping

    Total

    Total

    Total

    Complete Apps

    Socio Eco

    Pell Eligible

    Socio Eco

    First Gen

    Test Scores

    # W/ACT Scores

    Test Scores

    # W/SAT Scores

    Test Scores

    # W/Official Scores

    Test Scores

    # W/Self-Rpt Scores

    Test Scores

    ACT Mean

    Test Scores

    SAT Mean

    High School GPA

    Ave HS GPA

    High School GPA

    # Rank Reported

    High School GPA

    In Top 10%

    Sex

    Males

    Sex

    Females

    Round

    Regular

    Home Residence

    In State

    Home Residence

    Out of State

    Home Residence

    Non-U.S.

    Appl Type

    CommonApp

    Appl Type

    ApplyTexas

    Appl Type

    Other

     

     

     

    Stephen Forrest

     

    Institutional Research Analyst the Third

    University Decision Support

    Enrollment Management Research Group

    Office of the Provost

    Southern Methodist University

    sforrest@smu.edu

    214-768-4015

     

    "The only reason we put the data in, is to take the data out."

     

     

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 36.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 10:08 AM

    Here is something kind of simple, but nice for your end-users. Flatten output to one row per student and make user-friendly:

    FA award short description, status, and amount:
    LISTAGG(C.DESCRSHORT || ' (' || DECODE(B.AWARD_STATUS,'C','Cancelled','D','Declined','Accepted') || ') ' || B.DISBURSED_AMOUNT, ' – ') WITHIN GROUP (ORDER BY B.ITEM_TYPE)

    Output:
    Univ Gnt (Accepted) 1732 – UnsubLn (Accepted) 990 – UnsubLn2 (Accepted) 1979 – Plus Ln (Cancelled) 0

    Academic plan description and code:
    LISTAGG(B.DESCR || ' (' || B.ACAD_PLAN || ') ', ' / ')

    Output:
    Electromechanical Technology (ET-AASEEET) / Electr-Mechanical Technology (EMECT-AAS)

    FA Load by Term:
    LISTAGG(G.STRM || ' (' || G.FA_LOAD || ')', ' / ') WITHIN GROUP (ORDER BY G.STRM)

    Output:
    2205 (L) / 2208 (F)

    -------------------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 37.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 01:08 PM

    BTW, I'm saving all of these tricks.  Thank you all so much for sharing!!!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 38.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 10:11 AM
    I sometimes need a quick way to find test cases, based on the presence or absence of data in a particular field. I can set a criterion to either:
    "greater than" ! (if I want to find records with a value in a particular field)
    or
    "less than" ! (if I want to find records with no value in a particular field)
    Example below looks for students who have anything in the Degree Chkout Stat field in the ACAD_PROG table:

    SELECT A.DEGR_CHKOUT_STAT
      FROM PS_ACAD_PROG A
      WHERE ( A.EFFDT =
            (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
            WHERE A.EMPLID = A_ED.EMPLID
              AND A.ACAD_CAREER = A_ED.ACAD_CAREER
              AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
              AND A_ED.EFFDT <= SYSDATE)
        AND A.EFFSEQ =
            (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
            WHERE A.EMPLID = A_ES.EMPLID
              AND A.ACAD_CAREER = A_ES.ACAD_CAREER
              AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
              AND A.EFFDT = A_ES.EFFDT)
         AND A.PROG_STATUS = 'AC'
         AND A.DEGR_CHKOUT_STAT > '!')


    ------------------------------
    Patrick Beatty
    Assistant Director
    Michigan State 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!


  • 39.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 02:10 PM
    Edited by Elizabeth O'Connor 10-31-2025 02:11 PM

    For me, this is still my most used expression. I love the simplicity of it as you can just copy/paste a column of item types/student IDs, etc., and just dump them in a query:

    Student ID (399 max) or Item Type (307 max). Can copy a column of IDs or Item Types straight from excel and drop into expression. No translation values or excel formulas required.  This expression is used very frequently in our office:

    ·        Expression Type: Character, Length: 1

    ·        Instr('

    123456789

    987654321

    123654789

    789654123

    ',A.EMPLID,1,1)

    ·       Breaking down expression: INSTR('[your string values]',FIELD(FIELD ROW# FROM RECORD TABLE),START POSITION

    o   Example:

     A screenshot of a computer AI-generated content may be incorrect.



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

    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!


  • 40.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 03:33 PM

    Mine is a date-logic script I use to capture rows in JOB DATA where the rows in question may have "back-dated" Effective Dates. That is, rows that were physically entered in HCM within a given timeframe (determined by two prompt values) but have Effective Dates that fall before the start of the timeframe. This script limits the "look-back period" of the Effective Dates to X-number of months prior to the start date of the timeframe.  This script is particularly useful when the requested cohort is defined by the hire/rehire row in JOB DATA, such as "new-hire reports".

    SUBSTR( A.LASTUPDDTTM,0,10) BETWEEN TO_DATE(:1,'YYYY-MM-DD') AND TO_DATE(:2,'YYYY-MM-DD')
         AND A.EFFDT BETWEEN ADD_MONTHS((TO_DATE(:1,'YYYY-MM-DD')),-3) AND TO_DATE(:2,'YYYY-MM-DD'))



    ------------------------------
    Scott Frey
    Senior Data Analyst - Human Resource Information Systems
    University of Colorado System
    ------------------------------

    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!


  • 41.  RE: What's your favorite expression, case statement, trick?

    Posted 10-31-2025 07:13 PM

    It just about blew my mind when I learned that I can have an arbitrary sort order for the results of a SQL script.  For example:

    select * from ps_addresses
order by 
  case country
   when ' ' then '1'
   when 'USA' then '2'
   when 'CAN' then '3'
   when 'MEX' then '4'
   else country
end ;

    :exploding_head: 



    ------------------------------
    Ray Martin
    Enterprise Applications Engineer
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 42.  RE: What's your favorite expression, case statement, trick?

    Posted 11-03-2025 08:01 AM

    I just learned this trick too.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 43.  RE: What's your favorite expression, case statement, trick?

    Posted 11-03-2025 10:23 AM

    I also just learned this trick recently. We needed to organize an SID list by service indicators/active dates:

    REGEXP_REPLACE (LISTAGG(CASE WHEN J.SRVC_IND_CD IS NULL THEN NULL ELSE J.AUDIT_ACTN || ':' || J.SRVC_IND_CD || ':' || J.SRVC_IND_REASON || ':' || J.SRVC_IND_ACTIVE_DT END, ',') WITHIN GROUP (ORDER BY

          CASE

            WHEN J.SRVC_IND_CD = 'B52' THEN 1

            WHEN J.SRVC_IND_CD = 'B5W' THEN 2

            WHEN J.SRVC_IND_CD = 'B62' THEN 3

            ELSE 99

          END,

          J.SRVC_IND_ACTIVE_DT),'([^,]*)(,\1)+($|,)', '\1\3')



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

    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!


  • 44.  RE: What's your favorite expression, case statement, trick?

    Posted 11-03-2025 02:46 PM

    Because I'm such a nerd (and because not everyone likely knows this), you may have noticed that Ray and Elizabeth have shown 2 different  CASE syntaxes.  In Ray's, each "WHEN" will compare the same field (country, listed only once right after CASE) to each option.  In Elizabeth's, each "WHEN" can compare different fields (or multiple fields).   



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 45.  RE: What's your favorite expression, case statement, trick?

    Posted 30 days ago

    I love seeing so many great use cases and solutions on here!



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


  • 46.  RE: What's your favorite expression, case statement, trick?

    Posted 30 days ago

    I love this expression when I need to export a file to another system and want to pick which address or name (preferred vs primary):

    SUBSTR (MIN(CASE 
      WHEN K.ADDRESS_TYPE = 'DPLM' THEN '1'
      WHEN K.ADDRESS_TYPE = 'MAIL' THEN '2'
      WHEN K.ADDRESS_TYPE = 'HOME' THEN '3'
      ELSE '4'
      END || K.ADDRESS1),2,55)

    When I perform query training, I always instruct my users to change the "2" to a "1" on the last line and observe the logic in action and helps to verify the data.

    END || K.ADDRESS1),1,55)



    ------------------------------
    Lee Raubolt
    Associate Director of Admissions and Records
    Truckee Meadows Community College
    ------------------------------

    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!


  • 47.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    I just used this trick, Lee!   I needed to evaluate a table where a student would definitely have 2 rows but some would need row A and some would need row B and used this trick to pick the right row.   I love it!

     




    Original Message
    Alliance 2026 Registration is Open!


  • 48.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago

    I really like regexp_replace in statements to limit characters, etc that result in errors especially creating files to vendors.  

    here is an example from one of my many uses of it and Case is always good to use.

    Select
    Upper(convert(substr(regexp_replace(PD.LAST_NAME,'[^0-9A-Za-z.,]+['''']', ' '),1,45), 'US7ASCII' )) &PD.LAST_NAME,
    Upper(convert(substr(regexp_replace(PD.FIRST_NAME,'[^0-9A-Za-z.,]+['''']', ' '),1,45), 'US7ASCII' )) &PD.FIRST_NAME,
    Upper(convert(substr(regexp_replace(PD.MIDDLE_NAME,'[^0-9A-Za-z.,]+['''']', ' '),1,45), 'US7ASCII' )) &PD.MIDDLE_NAME,
    UPPER(PD.SEX)         &PD.SEX,
    UPPER(PD.MAR_STATUS) &PD.MAR_STATUS 

    FROM PS_PERSONAL_DATA PD
    WHERE PD.EMPLID = $EMPLID



    ------------------------------
    Tom Hins (tomhins@ou.edu)
    Senior Applications Developer
    University of Oklahoma
    Norman OK
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 49.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago
    Building on Elizabeth's INSTR Copy/Paste column of data (reply 27)

    I set up a prompt Length 3999 that allows the Paste to a run time optional prompt (:10) of up to 400 EMPLID's


    Using Grouped Criteria


    Bob
    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University



    Original Message
    Alliance 2026 Registration is Open!


  • 50.  RE: What's your favorite expression, case statement, trick?

    Posted 29 days ago

    It has been great to see all the different ways expressions are used by everyone. I know I am late to the party, but I wanted to add some formatting expressions that I have found useful.

    Address in one field (USA general format)

    Takes out blank values and adds returns [chr(10)] at the end of lines (E.DESCR is for county descr)

    CASE WHEN NVL(H.ADDRESS1, ' ') = ' ' THEN NULL ELSE H.ADDRESS1 || chr(10) END ||

    CASE WHEN NVL(H.ADDRESS2, ' ') = ' ' THEN NULL ELSE H.ADDRESS2 || chr(10) END ||

    CASE WHEN NVL(H.ADDRESS3, ' ') = ' ' THEN NULL ELSE H.ADDRESS3 || chr(10) END ||

    CASE WHEN NVL(H.ADDRESS4, ' ') = ' ' THEN NULL ELSE H.ADDRESS4 || chr(10) END ||

    CASE WHEN NVL(H.CITY , ' ') = ' ' THEN NULL ELSE H.CITY || ', ' END ||

    CASE WHEN NVL(H.STATE, ' ') = ' ' THEN NULL ELSE H.STATE || ' ' END ||

    H.POSTAL || chr(10) ||

    CASE WHEN H.COUNTRY = 'USA' THEN NULL ELSE E.DESCR END

    The CHR function helps format outputs in particular ways

    Time 12hr or 24hr format

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

    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

    I use checkboxes quite a bit to change the outputs to cover many different users.



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

    Original Message
    Alliance 2026 Registration is Open!


  • 51.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago

    Over time I've built a private library of queries.

     

    Whenever I have spent a lot of time and/or brainpower creating a complicated query or an expression, I save a basic, stripped-down version of the query as private, and label it in a way that gives me easy reference. Some queries are useful to copy, functioning as a foundation for a new query, so I don't have to spend time building a complex structure from scratch (union queries or multiple layers of outer joins). Others have scripts that I can review like reference notes.

     

    Having that available allows me to build accurate queries very quickly.

     

    Lynn

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 52.  RE: What's your favorite expression, case statement, trick?

    Posted 28 days ago

    Our admins do not like private queries ... makes their lives difficult, so I use the Folder under Query Properties for easy access. I make the Folder value to be 'TEMPLATE' and remove it then from the new query developed. 



    ------------------------------
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 53.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    Can you explain further? If this is tools/network access, that would not be feasible for everyone.

     

    What sort of trouble does this cause admins?

     

    Lynn

     




    Original Message
    Alliance 2026 Registration is Open!


  • 54.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago
    We refresh our test environment with a copy of our production environment every few months. Before we do, admins copy the public queries from our test environment then replace them so in process work is not lost. Private queries are not copied and are lost.

    That may just be a Penn State thing but our admins do not want us using Private

    Bob
    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University



    Original Message
    Alliance 2026 Registration is Open!


  • 55.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    We aren't supposed to have private queries (in PROD) either.   We are told not to migrate private queries (we write all queries in test).  We can do them in test but they get wiped out in refreshes. 


    What's different than Robert's set up is anything not migrated to PROD will be lost in a refresh.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 56.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    I am curious as to what the rationale is behind not allowing private queries. I have never heard of that being an issue.



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


  • 57.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    We don't have any prohibition on using private queries. We set a query to private when we don't want others using it, and public when we do. Our refreshes copy all queries from production. We make ad hoc requests to save queries before a refresh or copy them to production.



    ------------------------------
    Kevin Shalla
    Director of Technology
    University of Chicago
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 58.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    I am also curious. We have certain individuals within our units of FA/SF/AD/RO who have the ability to create both public and private queries within our areas in all environments. We have naming conventions that must be followed for public queries, and we are responsible for private query cleanup, but we are large institution that has four campuses and so have a vast catalog of queries. 



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

    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!


  • 59.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    I think the rationale at our school is that we have to get it migrated and they can't see it if it's private.   

     




    Original Message
    Alliance 2026 Registration is Open!


  • 60.  RE: What's your favorite expression, case statement, trick?

    Posted 24 days ago

    Migrations may indeed be the difference between being allowed to use private or not. We write queries in production, so we almost never migrate queries. If we do migrate (sometimes with upgrades or new functionality), we have a naming convention for private vs public, so it shouldn't be an issue.

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 61.  RE: What's your favorite expression, case statement, trick?

    Posted 27 days ago

    No, we don't have a prohibition against private queries, and in fact part of our very old naming conventions include ending the query with a 'V' to show it is private. Public queries end with a 'P'. We also refresh lower environments as you do, but the private queries are included.

     

    Interesting that Universities have different rules in that regard. I would hate to lose private queries.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 62.  RE: What's your favorite expression, case statement, trick?

    Posted 23 days ago

    Hey, All!  Vic Goldberg asked that we compile the tips & tricks in this post into a Google Doc and post it to the community home page.  We've done this.  Direct link to the Google Doc:  https://docs.google.com/document/d/1n7cs9rW15tYZYO91Xs73Dysqk5pS0o2humDYZLE_V7Y/edit?tab=t.0

    If you scroll down on the community home for the PS Query and SQL list, you'll see a button that will also take you to the Google Doc.  

    This document is PUBLIC so feel free to add a tip/trick (does not have to be an expression).   Make sure to add it to the contents list and also add a "posted by" so we know who to thank for the tip, trick, hack, whatever.  I tried to keep in alphabetical order.  Also, if I got something wrong that you posted, feel free to fix it.

    Let's keep the tips coming.  This has been fun!  



    ------------------------------
    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.
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 63.  RE: What's your favorite expression, case statement, trick?

    Posted 23 days ago

    And if you'd rather I add the tip for you, feel free to just keep responding to this post and I'll make sure it gets added to the Google Doc.

     




    Original Message
    Alliance 2026 Registration is Open!


  • 64.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    Thank you for doing this Dana!  I have found this thread to be both interesting and helpful, and having all the posts in a single, easy to read page is great. 

    seeya



    ------------------------------
    Tom Johnson
    Sr Business Systems Analyst
    Duke University
    tom.johnson@duke.edu
    "None of us is as smart as all of us"
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 65.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago

    This is great! Thank you for making the Google Doc! 



    ------------------------------
    Stephanie Turner
    Systems Analyst
    Rice 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!


  • 66.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    This is awesome, Dana!



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


  • 67.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    I find this manual unpivot trick comes in handy.  If we have a field (e.g. "TREE_NODE" that contains values like 'GENERALFEES' , 'TECHNOLOGY'  , etc,  and our user wants those amounts broken out in separate columns, with one row per emplid:

    expr1:
    SUM(
      CASE WHEN A.TREE_NODE = 'GENERALFEES' THEN A.TP_AMT ELSE 0 END
    ) OVER (PARTITION BY A.EMPLID) AS GENERAL_FEES

    expr2:
    SUM(
      CASE WHEN A.TREE_NODE = 'TECHNOLOGY' THEN A.TP_AMT ELSE 0 END
    ) OVER (PARTITION BY A.EMPLID) AS TECHNOLOGY  AS TECHNOLOGY

    etc. etc. 

    Note: since this method uses window functions, don't check "Aggregate Function" in the Expression Properties or Query Manager will get angry.


    Kendall Vance

    IT Technical Associate for Financial Aid Reporting
    Northern Illinois Univ



    ------------------------------
    Kendall Vance
    IT Technical Associate for Financial Aid Reporting
    Northern Illinois 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!


  • 68.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago
    Edited by Robert Lang 21 days ago

    I've also used windowing functions to "flatten" results in a manner similar to the one described here, and I thought it might be useful for folks unfamiliar with this technique to know that the "Distinct" box should be checked in the query properties (or however the user would add the DISTINCT keyword to the Select statement at the top level of the query), since the windowing functions themselves aren't actually flattening anything; rather, they're just generating a constant value (the one corresponding to the row sought after within the window) in the field created by the expression.

     For example, you'd initially get something like the following table for the first two IDs, assuming for illustrative purposes only 3 values for TREE_NODE ('GENERALFEES', 'TECHNOLOGY', and 'OTHER')-these values make up the "window" of the windowing function (i.e., the group of rows, or partitions, defined by the field specified in the PARTITION BY clause, which in this case is EMPLID):

    EMPLID     GENERAL_FEES     TECHNOLOGY     OTHER

    0000001    $159.00                     $224.12                  $22.37

    0000001    $159.00                     $224.12                  $22.37

    0000001    $159.00                     $224.12                  $22.37

    0000002    $115.00                     $126.70                  $72.50

    0000002    $115.00                     $126.70                  $72.50

    0000002    $115.00                     $126.70                  $72.50

    Applying the DISTINCT keyword would then deduplicate the table by all fields, resulting in the following, flattened/unpivoted/widened version of the above:

    EMPLID     GENERAL_FEES     TECHNOLOGY     OTHER

    0000001    $159.00                     $224.12                  $22.37

    0000002    $115.00                     $126.70                  $72.50

    It's important to note, however, that any other fields excluded from the Select statement but that exist in the records used in the query and that vary on the partitioning field may contribute to the size of the window, therefore requiring a more complex windowing function to achieve the desired result with an ordered windowing function (e.g., NTH_VALUE), typically by adding said field or fields to the PARTITION BY clause as applicable to cycle through unique values; the ORDER BY clause is important here. 



    ------------------------------
    Robert Lang
    Research Analyst
    Duke 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!


  • 69.  RE: What's your favorite expression, case statement, trick?

    Posted 22 days ago

    Thanks, I've added this to the google doc and basically created a "flattened" section.  😊

     




    Original Message
    Alliance 2026 Registration is Open!


  • 70.  RE: What's your favorite expression, case statement, trick?

    Posted 21 days ago
      |   view attached

    Attached is a quick tutorial I came up with for using NTH_VALUE.  In short, it is another way to flatten data.  When dealing with students who may have various item types and various numbers of those awards, it can sequence them in order for which they were awarded. 

    For instance, student A has three scholarships and they are at sequence 10, 20, 30.  Student B has two scholarships at sequence 10 and 20.  Scholarships for both students are different. My conundrum was that if I used item types in my query, there may be columns that were empty and then columns that were full causing gaps.  I didn't want that. NTH_VALUE allowed me to make this work.



    ------------------------------
    Daniel Labrecque
    Oracle Consulting Manager
    Huron Consulting Group
    ------------------------------

    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!


Related Content