PS Query & SQL

 View Only

  • 1.  Expression Help!

    Posted 5 days ago

    Hello Friends!

    Can I please get some expert help to write an expression for PS Query (Not SQL) for the following SQL that looks at ITEM_LINE_SF where field PAYMENT_ID_NBR appears more than once, drop all rows with that PAYMENT_ID_NBR. The goal is to not show the credit and reversal on the account within a 3rd party system. Delivered in PS this already happens.



    ------------------------------
    Lidia Anderson
    Manager, Campus Solutions
    Central Washington University
    lidia.anderson@cwu.edu
    ------------------------------
    HEUG ANZ - Brisbane, 9-10 September 2026


  • 2.  RE: Expression Help!

    Posted 5 days ago
    Hi Lidia,

    Is it true for you all that you will only have multiple PAYMENT_ID_NBRs in the case of credits reversals? I think we are set-up in such a way that we can have several payment IDs for a variety of reasons - although this is a bit outside of my wheelhouse.

    From a Query Manager perspective, to remove any instance of multiple PAYMENT_ID_NBRs I wonder if you can just get away with creating a COUNT() expression, and setting HAVING criteria against it:

    image.png
    1. Write the expression (make sure the Aggregate Function box is checked). 

    You might want to leave the "Distinct" part of the expression out, but I think you will want to use it. I would suggest trying it both ways because it will likely make a big difference.

    2. Return the count as a field. This is important because it creates "Group By" logic in the SQL, that tells the query how to partition the Count.

    3. Add The criteria, setting the expression to be "not greater than" 1:

    image.png

    If you run the query, it should drop all instances where there is more than 1 PAYMENT_ID_NBR.

    You will not find the criteria in the "Criteria" tab, but instead it will exist on the "Having" tab - where aggregate filtering sits.

    Let me know if you want to talk through it a bit more. :)

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



    HEUG ANZ - Brisbane, 9-10 September 2026


  • 3.  RE: Expression Help!

    Posted 5 days ago

    Hello Lidia,  

    On table ITEM_SF, a single PAYMENT_ID_NBR value may appear on several rows for any unique combination of BUSINESS_UNIT, COMMON_ID, and SA_ID_TYPE. This is based on how a payment is split. 

    Table ITEM_LINE_SF is a child record of ITEM_SF. I generally think of ITEM_LINE_SF as a series of individual events that aggregate up to ITEM_SF. Those events include payment reversals but also include other adjustments. 

    If you want to exclude payments that no longer have a dollar amount on ITEM_SF, then add criteria: ITEM_SF.ITEM_AMT <> 0

    If you want to exclude payments that were reversed, then join PAYMENT_TBL (on BUSINESS_UNIT, COMMON_ID, SA_ID_TYPE, and PAYMENT_ID_NBR) and add criteria: SF_REVERSAL_IND <> 'Y'.  Note: You could also use a DOES NOT EXIST. 

    Primary keys for

    • ITEM_SF: BUSINESS_UNIT, COMMON_ID, SA_ID_TYPE, ITEM_NBR
    • ITEM_LINE_SF: BUSINESS_UNIT, COMMON_ID, SA_ID_TYPE, ITEM_NBR, LINE_SEQ_NBR
    • PAYMENT_TBL: BUSINESS_UNIT, COMMON_ID, SA_ID_TYPE, PAYMENT_ID_NBR


    ------------------------------
    Jared Jakeman
    App Development and Integrations
    Central Washington University
    ------------------------------

    HEUG ANZ - Brisbane, 9-10 September 2026


  • 4.  RE: Expression Help!

    Posted 4 days ago

    Thank you to everyone for the useful and prompt responses. I was able to update my query and obtain the results I expected.

    Lidia



    ------------------------------
    Lidia Anderson
    Manager, Campus Solutions
    Central Washington University
    lidia.anderson@cwu.edu
    ------------------------------

    Original Message
    HEUG ANZ - Brisbane, 9-10 September 2026


Related Content