Hello everyone, and happy summer! I hope you're all making progress on those big projects while still finding a little time to relax. I can't believe we're already halfway through July!
I'm hoping someone can point me in the right direction with a PeopleSoft Query UNION issue.
I have an existing query that feeds a WorkCenter, and I recently discovered it isn't returning all the students I need. I created a second query that successfully finds the missing students, and my goal is to UNION the results so everything is returned in a single query.
Unfortunately, I haven't been able to get the UNION to work. AI keeps telling me there's a problem with the GROUP BY in the new query, but after trying several different approaches, I'm still stuck.
Has anyone run into something similar, or have suggestions on how to:
- Simplify my original query so it returns everything without needing a UNION, or
- Correctly UNION these two queries together?
My fallback plan is to run both queries separately in the WorkCenter, but I'd really like to avoid that if possible.
I've included both the original query and the new query below. Any suggestions or ideas would be greatly appreciated. I'm willing to try just about anything at this point!
Thank you in advance for your help!
Here is my original query:
|
SELECT A.EMPLID, CASE
WHEN B.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN B.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE D.FIRST_NAME
END, E.DESCR, regexp_replace(ListAgg (( TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) within group (order by TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')
FROM PS_TRNS_CRSE_TERM A, (PS_EXT_COURSE B LEFT OUTER JOIN PS_NAMES_FERPA_VW D ON B.LASTUPDOPRID = D.EMPLID ), PS_TRNS_CRSE_SCH C, PS_EXT_ORG_TBL E, PS_ADM_APPL_DATA N, PS_ADM_APPL_SCTY N1
WHERE ( C.EMPLID = A.EMPLID
AND C.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = A.INSTITUTION
AND C.MODEL_NBR = A.MODEL_NBR
AND N.EMPLID = N1.EMPLID
AND N.ACAD_CAREER = N1.ACAD_CAREER
AND N.ADM_APPL_NBR = N1.ADM_APPL_NBR
AND N1.OPRCLASS = 'HCPPALL'
AND N1.OPRID = 'xxxxxxxxx'
AND ( A.ACAD_CAREER = 'UGRD'
AND A.INSTITUTION = 'TMCC1'
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.LASTUPDDTTM > TO_DATE('2025-11-23','YYYY-MM-DD')
AND C.EXT_ORG_ID = B.EXT_ORG_ID
AND E.EXT_ORG_ID = C.EXT_ORG_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_EXT_ORG_TBL E_ED
WHERE E.EXT_ORG_ID = E_ED.EXT_ORG_ID
AND E_ED.EFFDT <= SYSDATE)
AND A.POST_DT = (SELECT MAX( F.POST_DT)
FROM PS_TRNS_CRSE_TERM F)
AND B.LASTUPDDTTM = (SELECT MAX( G.LASTUPDDTTM)
FROM PS_EXT_COURSE G)
AND A.EMPLID = N.EMPLID
AND A.ACAD_CAREER = N.ACAD_CAREER
AND A.INSTITUTION = N.INSTITUTION
AND ( TRUNC( B.LASTUPDDTTM) > TRUNC( A.POST_DT)
OR TRUNC( B.LASTUPDDTTM) <> TRUNC( A.POST_DT)) ))
GROUP BY A.EMPLID, CASE
WHEN B.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN B.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE D.FIRST_NAME
END, E.DESCR
UNION
SELECT H.EMPLID, CASE
WHEN H.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN H.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE L.FIRST_NAME
END, I.DESCR, regexp_replace(ListAgg (( TO_CHAR(CAST((H.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) within group (order by TO_CHAR(CAST((H.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')
FROM (PS_EXT_COURSE H LEFT OUTER JOIN PS_NAMES_FERPA_VW L ON H.LASTUPDOPRID = L.EMPLID ), PS_EXT_ORG_TBL I, PS_ADM_APPL_DATA M, PS_ADM_APPL_SCTY M1
WHERE ( M.EMPLID = M1.EMPLID
AND M.ACAD_CAREER = M1.ACAD_CAREER
AND M.ADM_APPL_NBR = M1.ADM_APPL_NBR
AND M1.OPRCLASS = 'HCPPALL'
AND M1.OPRID = 'xxxxxxxxx'
AND ( H.LASTUPDDTTM > TO_DATE('2025-11-01','YYYY-MM-DD')
AND H.EXT_ORG_ID = I.EXT_ORG_ID
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_EXT_ORG_TBL I_ED
WHERE I.EXT_ORG_ID = I_ED.EXT_ORG_ID
AND I_ED.EFFDT <= SYSDATE)
AND NOT EXISTS (SELECT 'X'
FROM PS_TRNS_CRSE_TERM J, PS_TRNS_CRSE_SCH K
WHERE J.EMPLID = H.EMPLID
AND J.EMPLID = K.EMPLID
AND J.ACAD_CAREER = K.ACAD_CAREER
AND J.INSTITUTION = K.INSTITUTION
AND J.MODEL_NBR = K.MODEL_NBR
AND K.EXT_ORG_ID = H.EXT_ORG_ID
AND J.MODEL_STATUS = 'P'
AND J.INSTITUTION = H.INSTITUTION)
AND H.INSTITUTION = 'TMCC1'
AND H.EMPLID = M.EMPLID ))
GROUP BY H.EMPLID, CASE
WHEN H.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN H.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE L.FIRST_NAME
END, I.DESCR
ORDER BY 4
|
And this is the query I'm trying to Union onto that original query:
|
SELECT DISTINCT A.EMPLID, CASE
WHEN A.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN A.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE D.FIRST_NAME
END, C.DESCR, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
FROM (PS_EXT_COURSE A LEFT OUTER JOIN PS_NAMES_FERPA_VW D ON A.LASTUPDOPRID = D.EMPLID ), PS_EXT_ORG_TBL C
WHERE ( A.INSTITUTION = 'TMCC1'
AND A.TERM_YEAR > 2025
AND A.LASTUPDDTTM > (SELECT MAX( B.POST_DT+1)
FROM PS_TRNS_CRSE_TERM B, PS_TRNS_CRSE_SCH E
WHERE B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.MODEL_STATUS = 'P'
AND B.EMPLID = E.EMPLID
AND B.ACAD_CAREER = E.ACAD_CAREER
AND B.INSTITUTION = E.INSTITUTION
AND B.MODEL_NBR = E.MODEL_NBR
AND E.EXT_ORG_ID = A.EXT_ORG_ID)
AND A.EXT_ORG_ID = C.EXT_ORG_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_EXT_ORG_TBL C_ED
WHERE C.EXT_ORG_ID = C_ED.EXT_ORG_ID
AND C_ED.EFFDT <= SYSDATE))
ORDER BY 4
|
Lee
------------------------------
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.
------------------------------