PS Query & SQL

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

I would do something like this:

SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, B.REQ_TERM, C.DESCR, C.ACAD_PLAN_TYPE
  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_ACAD_PLAN_TBL C
  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 IN ('AC','LA')
     AND A.ACAD_PROG = 'UG'
-------------------------------------------------------------------------------------------------     
     and c.acad_plan_type='RCE'
     and exists(select 'x' from PS_ACAD_PLAN_TBL C_nbr
                 where C_nbr.EFFDT = (SELECT MAX(C_nbrED.EFFDT) FROM PS_ACAD_PLAN_TBL C_nbrED
                                                               WHERE C_nbr.INSTITUTION = C_nbrED.INSTITUTION
                                                               AND C_nbr.ACAD_PLAN = C_nbrED.ACAD_PLAN
                                                               AND C_nbrED.EFFDT <= SYSDATE)
                 and b.acad_plan = c_nbr.acad_plan
                 group by c_nbr.acad_plan_type
                 having count(distinct c_nbr.acad_plan_type) = 1                                  
                )
 ----------------------------------------------------------------------------------------------                                   
                                    
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.ACAD_CAREER = B_ED.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ =
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PLAN B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.ACAD_CAREER = B_ES.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
          AND B.EFFDT = B_ES.EFFDT)
     AND B.ACAD_PLAN = C.ACAD_PLAN
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C_ED
        WHERE C.INSTITUTION = C_ED.INSTITUTION
          AND C.ACAD_PLAN = C_ED.ACAD_PLAN
          AND C_ED.EFFDT <= SYSDATE))

------------------------------
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:
Sent: 09-15-2025 05:33 PM
From: Lidia Anderson
Subject: Query help ... only students who have one plan type and it's equal to 'RCE'

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

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

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

Here would be my interpretation of how I would adjust your SQL further:

SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, B.REQ_TERM, C.DESCR, C.ACAD_PLAN_TYPE
  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_ACAD_PLAN_TBL C
  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 IN ('AC','LA')
     AND A.ACAD_PROG = 'UG'
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND B.EFFDT = A.EFFDT
        (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.ACAD_CAREER = B_ED.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ = A.EFFSEQ
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PLAN B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.ACAD_CAREER = B_ES.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
          AND B.EFFDT = B_ES.EFFDT)
     AND B.ACAD_PLAN = C.ACAD_PLAN
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C_ED
        WHERE C.INSTITUTION = C_ED.INSTITUTION
          AND C.ACAD_PLAN = C_ED.ACAD_PLAN
          AND C_ED.EFFDT <= SYSDATE))

     AND C.ACAD_PLAN_TYPE = 'RCE'

     AND NOT EXISTS

         (SELECT 'X'

          FROM PS_ACAD_PROG A2, PS_ACAD_PLAN B2, PS_ACAD_PLAN_TBL C2

          WHERE A2.EMPLID = A.EMPLID

                  AND A2.EFFDT = (SELECT MAX(EFFDT)  FROM PS_ACAD_PROG A2_ED

                          WHERE A2_ED.EMPLID = A2.EMPLID

                                 AND A2_ED.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ED.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ED.EFFDT <= SYSDATE)

                  AND A2.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_ACAD_PROG A2_ES

                           WHERE A2_ES.EMPLID = A2.EMPLID

                                 AND A2_ES.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ES.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ES.EFFDT = A2.EFFDT)

                  AND A2. ROG_STATUS IN ('AC','LA')
                  AND A2.ACAD_PROG = 'UG'

                  AND B2.EMPLID = A2.EMPLID

                  AND B2.ACAD_CAREER = A2.ACAD_CAREER

                  AND B2.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                  AND B2.EFFDT = A2.EFFDT

                  AND B2.EFFSEQ = A2.EFFSEQ

                  AND C2.ACAD_PLAN = B2.ACAD_PLAN   

                  AND C2.EFFDT =
                        (SELECT MAX(C2_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C2_ED
                         WHERE C2.INSTITUTION = C2_ED.INSTITUTION
                               AND C2.ACAD_PLAN = C2_ED.ACAD_PLAN
                               AND C2_ED.EFFDT <= SYSDATE))

                  AND C2.ACAD_DEGREE_TYPE = 'MAJ')                

My adjustments:

1. EFFDT/EFFSEQ logic for a child table should always be set to match the parent table.  Therefore whenever joining ACAD_PLAN with ACAD_PROG, only do the Max subqueries on ACAD_PROG and then match on the EFFDT and EFFSEQ columns between ACAD_PROG and ACAD_PLAN.
2. The above takes in the possibility that you could have more than one ACAD_CAREER and STDNT_CAR_NBR in a Student's Program/Plan stack both related to ACAD_PROG = 'UG'.  I'm not sure if that is allowable in your institution or not.  But, by nature, the system does provide for multiple ACAD_CAREER values as well as multiple STDNT_CAR_NBR values per EMPLID.  The above includes a Not Exist clause to filter out anyone who has another EMPLID row associated with an ACAD_PLAN that has an ACAD_PLAN_TYPE = 'MAJ'.  Would this be correct in your institution, I'm not really sure since I'm not sure how you are classifying "another major".  If you have a different definition of which types of Plans make up a Major, you would just need to tweak that criteria in the Not Exists clause.  

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

Here would be my interpretation of how I would adjust your SQL further:

SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, B.REQ_TERM, C.DESCR, C.ACAD_PLAN_TYPE
  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_ACAD_PLAN_TBL C
  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 IN ('AC','LA')
     AND A.ACAD_PROG = 'UG'
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND B.EFFDT = A.EFFDT
        (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.ACAD_CAREER = B_ED.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ = A.EFFSEQ
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PLAN B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.ACAD_CAREER = B_ES.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
          AND B.EFFDT = B_ES.EFFDT)
     AND B.ACAD_PLAN = C.ACAD_PLAN
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C_ED
        WHERE C.INSTITUTION = C_ED.INSTITUTION
          AND C.ACAD_PLAN = C_ED.ACAD_PLAN
          AND C_ED.EFFDT <= SYSDATE))

     AND C.ACAD_PLAN_TYPE = 'RCE'

     AND NOT EXISTS

         (SELECT 'X'

          FROM PS_ACAD_PROG A2, PS_ACAD_PLAN B2, PS_ACAD_PLAN_TBL C2

          WHERE A2.EMPLID = A.EMPLID

                  AND A2.EFFDT = (SELECT MAX(EFFDT)  FROM PS_ACAD_PROG A2_ED

                          WHERE A2_ED.EMPLID = A2.EMPLID

                                 AND A2_ED.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ED.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ED.EFFDT <= SYSDATE)

                  AND A2.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_ACAD_PROG A2_ES

                           WHERE A2_ES.EMPLID = A2.EMPLID

                                 AND A2_ES.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ES.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ES.EFFDT = A2.EFFDT)

                  AND A2. ROG_STATUS IN ('AC','LA')
                  AND A2.ACAD_PROG = 'UG'

                  AND B2.EMPLID = A2.EMPLID

                  AND B2.ACAD_CAREER = A2.ACAD_CAREER

                  AND B2.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                  AND B2.EFFDT = A2.EFFDT

                  AND B2.EFFSEQ = A2.EFFSEQ

                  AND C2.ACAD_PLAN = B2.ACAD_PLAN   

                  AND C2.EFFDT =
                        (SELECT MAX(C2_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C2_ED
                         WHERE C2.INSTITUTION = C2_ED.INSTITUTION
                               AND C2.ACAD_PLAN = C2_ED.ACAD_PLAN
                               AND C2_ED.EFFDT <= SYSDATE))

                  AND C2.ACAD_DEGREE_TYPE = 'MAJ')                

My adjustments:

1. EFFDT/EFFSEQ logic for a child table should always be set to match the parent table.  Therefore whenever joining ACAD_PLAN with ACAD_PROG, only do the Max subqueries on ACAD_PROG and then match on the EFFDT and EFFSEQ columns between ACAD_PROG and ACAD_PLAN.
2. The above takes in the possibility that you could have more than one ACAD_CAREER and STDNT_CAR_NBR in a Student's Program/Plan stack both related to ACAD_PROG = 'UG'.  I'm not sure if that is allowable in your institution or not.  But, by nature, the system does provide for multiple ACAD_CAREER values as well as multiple STDNT_CAR_NBR values per EMPLID.  The above includes a Not Exist clause to filter out anyone who has another EMPLID row associated with an ACAD_PLAN that has an ACAD_PLAN_TYPE = 'MAJ'.  Would this be correct in your institution, I'm not really sure since I'm not sure how you are classifying "another major".  If you have a different definition of which types of Plans make up a Major, you would just need to tweak that criteria in the Not Exists clause.  

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.

The post did not bring forward any highlighting, but I'm making the assumption that you are referring to the ACAD_PROG and PROG_STATUS criteria I kept in the Not Exists clause.  I definitely could have joined the A2.ACAD_PROG  to the A.ACAD_PROG to eliminate the value references in case something would change.  However, the PROG_STATUS would need to stay specified to avoid limiting the search for additional plans only to the PROG_STATUS of the original ACAD_PROG row.

As for the EFFDT logic on the ACAD_PLAN_TBL... I have always wondered, but never took the time to see how the system built the SQL for that table when pulling into a page.  While it is true that the ACAD_PLAN_TBL row must exist as of the EFFDT of the ACAD_PLAN row, I've wondered whether any effective dated changes to the ACAD_PLAN_TBL would also require a new ACAD_PROG entry for the change to take place.  I didn't think it would.  I thought the system would still be pulling the most current effective date from the ACAD_PLAN_TBL regardless of the EFFDT on ACAD_PROG.

Here would be my interpretation of how I would adjust your SQL further:

SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, B.REQ_TERM, C.DESCR, C.ACAD_PLAN_TYPE
  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_ACAD_PLAN_TBL C
  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 IN ('AC','LA')
     AND A.ACAD_PROG = 'UG'
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND B.EFFDT = A.EFFDT
        (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.ACAD_CAREER = B_ED.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
          AND B_ED.EFFDT <= SYSDATE)
    AND B.EFFSEQ = A.EFFSEQ
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PLAN B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.ACAD_CAREER = B_ES.ACAD_CAREER
          AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
          AND B.EFFDT = B_ES.EFFDT)
     AND B.ACAD_PLAN = C.ACAD_PLAN
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C_ED
        WHERE C.INSTITUTION = C_ED.INSTITUTION
          AND C.ACAD_PLAN = C_ED.ACAD_PLAN
          AND C_ED.EFFDT <= SYSDATE))

     AND C.ACAD_PLAN_TYPE = 'RCE'

     AND NOT EXISTS

         (SELECT 'X'

          FROM PS_ACAD_PROG A2, PS_ACAD_PLAN B2, PS_ACAD_PLAN_TBL C2

          WHERE A2.EMPLID = A.EMPLID

                  AND A2.EFFDT = (SELECT MAX(EFFDT)  FROM PS_ACAD_PROG A2_ED

                          WHERE A2_ED.EMPLID = A2.EMPLID

                                 AND A2_ED.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ED.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ED.EFFDT <= SYSDATE)

                  AND A2.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_ACAD_PROG A2_ES

                           WHERE A2_ES.EMPLID = A2.EMPLID

                                 AND A2_ES.ACAD_CAREER = A2.ACAD_CAREER

                                 AND A2_ES.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                                 AND A2_ES.EFFDT = A2.EFFDT)

                  AND A2. ROG_STATUS IN ('AC','LA')
                  AND A2.ACAD_PROG = 'UG'

                  AND B2.EMPLID = A2.EMPLID

                  AND B2.ACAD_CAREER = A2.ACAD_CAREER

                  AND B2.STDNT_CAR_NBR = A2.STDNT_CAR_NBR

                  AND B2.EFFDT = A2.EFFDT

                  AND B2.EFFSEQ = A2.EFFSEQ

                  AND C2.ACAD_PLAN = B2.ACAD_PLAN   

                  AND C2.EFFDT =
                        (SELECT MAX(C2_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C2_ED
                         WHERE C2.INSTITUTION = C2_ED.INSTITUTION
                               AND C2.ACAD_PLAN = C2_ED.ACAD_PLAN
                               AND C2_ED.EFFDT <= SYSDATE))

                  AND C2.ACAD_DEGREE_TYPE = 'MAJ')                

My adjustments:

1. EFFDT/EFFSEQ logic for a child table should always be set to match the parent table.  Therefore whenever joining ACAD_PLAN with ACAD_PROG, only do the Max subqueries on ACAD_PROG and then match on the EFFDT and EFFSEQ columns between ACAD_PROG and ACAD_PLAN.
2. The above takes in the possibility that you could have more than one ACAD_CAREER and STDNT_CAR_NBR in a Student's Program/Plan stack both related to ACAD_PROG = 'UG'.  I'm not sure if that is allowable in your institution or not.  But, by nature, the system does provide for multiple ACAD_CAREER values as well as multiple STDNT_CAR_NBR values per EMPLID.  The above includes a Not Exist clause to filter out anyone who has another EMPLID row associated with an ACAD_PLAN that has an ACAD_PLAN_TYPE = 'MAJ'.  Would this be correct in your institution, I'm not really sure since I'm not sure how you are classifying "another major".  If you have a different definition of which types of Plans make up a Major, you would just need to tweak that criteria in the Not Exists clause.  

Hello,

I am writing a query to find UG degree-seeking students who are only in a certificate program plan type of 'RCE' and no other major declared on their program stack. We have to ensure that the students have a Financial Aid eligible major associated with their student program stack. I have the base started, but I'm having trouble wrapping my head around the best way to pull all students who fit the criteria. The base below pulls everyone with a plan code and their type, but isn't limiting the results and requires filtering, and I'd like to avoid that.