PS Query & SQL

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

Pulling Transfer Credit Grades

  • 1.  Pulling Transfer Credit Grades

    Posted 04-29-2025 06:08 PM

    Hello,

    I am trying to pull in transfer credit grades into a query with institution grades but when I record TRNS_CRSE_DTL I get multiple rows and I have also tried pulling in TRNS_CRSE_TERM to include a field by the term but still have issues and the articulation term populates are courses regardless of transfer or not. I am using outer joins in the event the student doesn't have transfer credits. The SQL is my baseline before adding the transfer records.

    SELECT DISTINCT A.EMPLID, B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
      FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
      WHERE ( A.EMPLID = '24554411'
         AND 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.EMPLID = C.EMPLID
         AND A.ACAD_CAREER = C.ACAD_CAREER
         AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
         AND C.INSTITUTION = A.INSTITUTION)
      ORDER BY 2 DESC



    ------------------------------
    Lidia Anderson
    Manager, Campus Solutions
    Central Washington University
    lidia.anderson@cwu.edu
    ------------------------------
    Alliance 2026 Recordings are here!


  • 2.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 08:17 AM

    I would get rid of ACAD_PROG all together.  Start with STDNT_CAR_TERM and then do outer joins for both TRNS_CRSE_TERM and CLASS_TBL_SE_VW.  I think looking for the active program is taken care of by just grabbing STDNT_CAR_TERM.  



    ------------------------------
    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 Recordings are here!


  • 3.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 08:58 AM

    I tested that out.  I'm seeing it repeated also. 

     

    The articulation term is going to pull up each class each time.


    Maybe build it as a report and have separate queries.   I've got nothing at the moment. 

     




    Original Message
    Alliance 2026 Recordings are here!


  • 4.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:10 AM

    That's a good point, Dana. Maybe build it as a connected query.

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 5.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:12 AM
    I'm not sure I totally understand what you're trying to do, but I wrote a query with both transfer and institutional courses and grades, and I ended up using a union. 

    Have a PeopleSoft Campus Solutions or Slate CRM issue or need data? Submit a request

     

    signatureImage

    Joshua Wood
    Functional Analyst IV
    Campus Solutions Services
    University of Houston
    (713) 743-7163
    jwood6@uh.edu
    https://uh.edu

      

    CONFIDENTIALITY NOTICE: The information in this email may be confidential and/or privileged. If you are not the intended recipient, then any review, dissemination, or copying of this email and its attachments, if any, or the information contained herein is prohibited. If you have received this email in error, then please immediately notify the sender by return email and delete this email from your devices.





    Original Message
    Alliance 2026 Recordings are here!


  • 6.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:22 AM

    Hi Lidia,

    Similar to Josh, I would recommend a union. Starting with your baseline query, you can add TRNS_CRSE_DTL like this:

    SELECT A.EMPLID, 'Institution', B.STRM, B.SUBJECT, B.CATALOG_NBR, B.CRSE_GRADE_OFF
      FROM (PS_ACAD_PROG A LEFT OUTER JOIN  PS_CLASS_TBL_SE_VW B ON  A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND B.INSTITUTION = A.INSTITUTION ), PS_STDNT_CAR_TERM C
      WHERE ( A.EMPLID = :1
         AND 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.EMPLID = C.EMPLID
         AND A.ACAD_CAREER = C.ACAD_CAREER
         AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
         AND C.INSTITUTION = A.INSTITUTION)
    UNION
    SELECT D.EMPLID, 'Transfer', D.ARTICULATION_TERM, D.COMP_SUBJECT_AREA, D.TRNSFR_EQVLNCY_CMP, D.CRSE_GRADE_OFF
      FROM PS_TRNS_CRSE_DTL D
      WHERE ( D.EMPLID = :1)
      ORDER BY 3 DESC



    ------------------------------
    Sam Kunz
    Systems Analyst
    Boise State University
    samkunz@boisestate.edu
    ------------------------------

    Original Message
    Alliance 2026 Recordings are here!


  • 7.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:27 AM

    Thank you, Sam. I will give it a try. I did start with a union and couldn't get it to work, but I am sure it was user error.

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 8.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:28 AM

    I never think to do unions.  I need to do those more.  That's why I love the HEUG so much!

     




    Original Message
    Alliance 2026 Recordings are here!


  • 9.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:31 AM

    Agreed Dana! The HEUG is awesome!!!

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 10.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:42 AM

    Sam, thanks again. That worked. I see what I was doing wrong. I do have another question. If I only want the grades that are articulated towards a student's total credits, do I need to tie in the articulation term record? If so, are there things I need to be aware of?

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 11.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:25 AM

    Hi Joshua,

     

    I have an advisor who'd like to see the grades for his advisee list, including transfer grades. I tried a union but wasn't successful. Do you have insight into writing an effective union with transfer credit and the sample I provided?

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 12.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:37 AM
    I'm ashamed to say that it took me a couple of years of stops, starts, failures, and experience to get this right. The SQL is pretty big and ugly. 

    Also - I don't think I used a student or applicant table as a base because I wanted the courses regardless of their status, if that makes sense. 

    The main thing about unions is to match your fields. Even if you have to include blank columns in one query or the other. 

    Have a PeopleSoft Campus Solutions or Slate CRM issue or need data? Submit a request

     

    signatureImage

    Joshua Wood
    Functional Analyst IV
    Campus Solutions Services
    University of Houston
    (713) 743-7163
    jwood6@uh.edu
    https://uh.edu

      

    CONFIDENTIALITY NOTICE: The information in this email may be confidential and/or privileged. If you are not the intended recipient, then any review, dissemination, or copying of this email and its attachments, if any, or the information contained herein is prohibited. If you have received this email in error, then please immediately notify the sender by return email and delete this email from your devices.





    Original Message
    Alliance 2026 Recordings are here!


  • 13.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:44 AM

    Thanks for the tips, Josh. I knew about matching the fields, but including blanks is a new and useful tip! Dana is right, the HEUG is awesome!!

     

    Kind regards,

     

    Lidia

    Lidia Anderson
    Manager, Campus Solutions
    Enterprise Applications
    lidia.anderson@cwu.edu

    Support: mycwu-cs@cwu.edu

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 14.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 09:35 AM
    I get this from your SQL (I added the prog status column):


    Regarding duplicates, are referring to, for example, the highlighted rows?  The view CLASS_TBL_SE_VW is bringing in all enrollment (graded lecture, non-graded discussion in this example).  You can add criteria to tailor your results.

    Regarding a previous comment about removing ACAD_PROG.  Inclusion (or not) depends upon what students you want in your results.  If a student has multiple acad prog entries (UGRD and GRAD career for example), you might get duplicate results (although you may be comparing career and program values between the transfer data and acad prog so that would likely eliminate such a duplicate situation).  A student can be term activated and not an active student.  



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

    Duke University | Student Information Services & System (SISS)

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

    www.sissoffice.duke.edu





    Original Message
    Alliance 2026 Recordings are here!


  • 15.  RE: Pulling Transfer Credit Grades

    Posted 04-30-2025 10:30 AM

    Hi Lidia, 

    I have done some work with Transfer course posting - I tend to look at both detail , term, and school info, hope this helps.

    SELECT A.EMPLID, A.ACAD_CAREER, D.ACAD_PROG, A.MODEL_NBR, A.ARTICULATION_TERM,  A.MODEL_STATUS, A.MODEL_STATUS, A.UNT_TAKEN, A.UNT_TRNSFR, C.TRNSFR_EQVLNCY_GRP, C.TRNSFR_EQVLNCY_SEQ, C.TRNSFR_STAT, C.TRNSFR_SRC_ID, C.TRNSFR_EQVLNCY, F.TERM_YEAR, F.EXT_TERM, F.SCHOOL_SUBJECT, F.SCHOOL_CRSE_NBR, F.CRSE_GRADE_INPUT, B.SUBJECT, B.CATALOG_NBR, C.SSR_UNT_TAKEN_EXT, C.UNT_TAKEN, C.CRSE_ID, C.CRSE_OFFER_NBR, C.UNT_TRNSFR, C.CRSE_GRADE_INPUT, C.CRSE_GRADE_OFF, C.GRD_PTS_PER_UNIT, 
      FROM PS_TRNS_CRSE_TERM A, PS_TRNS_CRSE_DTL C, PS_CRSE_OFFER B, PS_TRNS_CRSE_SCH D, PS_EXT_COURSE F
      WHERE ( A.EMPLID = C.EMPLID
         AND A.ACAD_CAREER = C.ACAD_CAREER
         AND A.INSTITUTION = C.INSTITUTION
         AND A.MODEL_NBR = C.MODEL_NBR
         AND A.ARTICULATION_TERM = C.ARTICULATION_TERM
         AND C.ACAD_CAREER = B.ACAD_CAREER
         AND C.INSTITUTION = B.INSTITUTION
         AND B.CRSE_ID = C.CRSE_ID
         AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR
         AND B.EFFDT =
            (SELECT MAX(B_ED.EFFDT) FROM PS_CRSE_OFFER B_ED
            WHERE B.CRSE_ID = B_ED.CRSE_ID
              AND B_ED.EFFDT <= SYSDATE)
         AND A.ARTICULATION_TERM = :1
         AND A.EMPLID = D.EMPLID
         AND A.ACAD_CAREER = D.ACAD_CAREER
         AND A.INSTITUTION = D.INSTITUTION
         AND A.MODEL_NBR = D.MODEL_NBR
         AND C.EMPLID = F.EMPLID
         AND C.INSTITUTION = F.INSTITUTION
         AND F.EXT_COURSE_NBR = C.EXT_COURSE_NBR)

    Thanks,

    Mary



    ------------------------------
    Mary Lee
    Assistant for University Systems Analysis
    Stony Brook 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 Recordings are here!


Related Content