In case the other SQL doesn't format correctly:
SELECT
A.emplid,
A.acad_career,
A.stdnt_car_nbr,
To_char(A.effdt, 'YYYY-MM-DD'),
A.effseq,
A.acad_plan,
To_char(A.declare_dt, 'YYYY-MM-DD'),
A.plan_sequence
FROM
ps_acad_plan A
WHERE
(
A.effdt = (
SELECT
Max(A_ED.effdt)
FROM
ps_acad_plan 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_plan 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.plan_sequence = (
SELECT
Min(B.plan_sequence)
FROM
ps_acad_plan B
WHERE
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.emplid = A.emplid
AND B.acad_career = A.acad_career
AND B.stdnt_car_nbr = A.stdnt_car_nbr
AND B.effdt = A.effdt
AND B.effseq = A.effseq
)
)
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
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: 02-24-2025 01:22 PM
From: Daniel Labrecque
Subject: Minimum Plan Sequence
Rachel,
I built a version using my subquery and did not get the error. Here the SQL for it:
SELECT A.emplid, A.acad_career, A.stdnt_car_nbr, To_char(A.effdt, 'YYYY-MM-DD'), A.effseq, A.acad_plan, To_char(A.declare_dt, 'YYYY-MM-DD'), A.plan_sequenceFROM ps_acad_plan AWHERE ( A.effdt = (SELECT Max(A_ED.effdt) FROM ps_acad_plan 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_plan 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.plan_sequence = (SELECT Min(B.plan_sequence) FROM ps_acad_plan B WHERE 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.emplid = A.emplid AND B.acad_career = A.acad_career AND B.stdnt_car_nbr = A.stdnt_car_nbr AND B.effdt = A.effdt AND B.effseq = A.effseq) )
If you need screenshots, I can provide those as well.
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
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: 02-24-2025 01:12 PM
From: Rachael Daniel
Subject: Minimum Plan Sequence
And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out.
Rachael Daniel
Director, Systems & Data Analysis
Division of Strategic Enrollment Management & Student Success
University of Wisconsin-Milwaukee
414-229-4277
rdaniel@uwm.edu
My pronouns are she/her/hers
Original Message:
Sent: 2/24/2025 1:01:00 PM
From: Daniel Labrecque
Subject: RE: Minimum Plan Sequence
I think what you are wanting to use is a subquery on PLAN_SEQUENCE. In the subquery you will do your joins as usual, but will select the PLAN_SEQUENCE field and make that minimum.
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
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: 02-24-2025 12:50 PM
From: Rachael Daniel
Subject: Minimum Plan Sequence
Sorry, I should have been more clear. If a student has more than one plan, I want the one where, for this single career number, the PLAN_SEQUENCE is the minimum sequence. The dates there (for declare date or eff date) don't always equate the minimum plan sequence number. For example, I want to return ONLY the ACCBBA2 plan code, because it's the minimum sequence number.
Rachael Daniel
Director, Systems & Data Analysis
Division of Strategic Enrollment Management & Student Success
University of Wisconsin-Milwaukee
414-229-4277
rdaniel@uwm.edu
My pronouns are she/her/hers
Original Message:
Sent: 2/24/2025 12:43:00 PM
From: Daniel Labrecque
Subject: RE: Minimum Plan Sequence
Hello Rachel,
I think you can change the EFFDT criteria to:
By default it is set to Current Date (EffSeq = Last).
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.