PS Query & SQL

View Only

Alliance 2026 | March 8-11, 2026 | Orlando, FL

Back to discussions

Expand all | Collapse all

Union Query

1. Union Query

Recommend

Lee Raubolt

I'm on HEUG Marketplace!

Posted 5 days ago

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.
------------------------------

2. RE: Union Query

Recommend
Scott Frey
Posted 5 days ago
Good afternoon Lee,

I don't see anything super-obvious, but some of the fields you're using do involve A TON of "transition" operators. So I'm wondering if you have a field type/field width mismatch? Also, you have a SELECT DISTINCT statement in your new Union syntax but have simple SELECT statements in the other two levels.

What is the error you are seeing? Is it ORA-01790?

If you have access to the Alliance26 sessions, I recommend reviewing my sessions on creating Query Unions, 5823 & 5824, available here: https://www.heug.org/search?executeSearch=true&SearchTerm=alliance+2026+5823+creating+unioned+queries&l=1

------------------------------
Scott Frey
Senior Data Analyst-HR Info Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

3. RE: Union Query

Recommend

Lee Raubolt

I'm on HEUG Marketplace!

Posted 5 days ago

I'm currently getting this error, but as you can see in my screenshots below, I think I have the same fields at the top level and in my two unions. Maybe you can spot my mistake. I should have included the SQL for the query that has all the unions. Here is that SQL, and getting this error: An error occurred. Please consult your system log for details.

Error in running query because of SQL Error, Code=1789, Message=ORA-01789: query block has incorrect number of result columns (50,380)

Also, the distinct disappears when you create the union, as you can see in the SQL below. Thank you for your help. My head is starting to hurt from banging it on my desk. I'll check out your presentations now.

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 = '5002546687'
    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 = '5002546687'
    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
UNION
SELECT O.EMPLID, CASE
WHEN O.LASTUPDOPRID LIKE 'PStoPS_%'
       THEN 'EDI'
WHEN O.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
       THEN 'Raptor'
ELSE S.FIRST_NAME
END, R.DESCR, regexp_replace(ListAgg (( TO_CHAR(CAST((O.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) within group (order by TO_CHAR(CAST((O.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')
FROM (PS_EXT_COURSE O LEFT OUTER JOIN PS_NAMES_FERPA_VW S ON O.LASTUPDOPRID = S.EMPLID ), PS_EXT_ORG_TBL R
WHERE ( O.INSTITUTION = 'TMCC1'
     AND O.TERM_YEAR > 2025
     AND O.LASTUPDDTTM = (SELECT MAX( P.POST_DT+1)
FROM PS_TRNS_CRSE_TERM P, PS_TRNS_CRSE_SCH Q
WHERE P.EMPLID = O.EMPLID
     AND P.INSTITUTION = O.INSTITUTION
     AND P.MODEL_STATUS = 'P'
     AND P.EMPLID = Q.EMPLID
     AND P.ACAD_CAREER = Q.ACAD_CAREER
     AND P.INSTITUTION = Q.INSTITUTION
     AND P.MODEL_NBR = Q.MODEL_NBR
     AND Q.EXT_ORG_ID = O.EXT_ORG_ID)
     AND O.EXT_ORG_ID = R.EXT_ORG_ID
     AND R.EFFDT =
        (SELECT MAX(R_ED.EFFDT) FROM PS_EXT_ORG_TBL R_ED
        WHERE R.EXT_ORG_ID = R_ED.EXT_ORG_ID
          AND R_ED.EFFDT <= SYSDATE))
GROUP BY O.EMPLID, CASE
WHEN O.LASTUPDOPRID LIKE 'PStoPS_%'
       THEN 'EDI'
WHEN O.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
       THEN 'Raptor'
ELSE S.FIRST_NAME
END, R.DESCR
ORDER BY 4

Thank you!

Lee

----------------------------------------

Lee Raubolt, M.S.

Truckee Meadows Community College

Associate Director of Admissions & Enrollment Services

7000 Dandini Blvd.

RDMT 319

Reno, NV 89512 USA

775-674-7624

lraubolt@tmcc.edu

On Wed, Jul 8, 2026 at 3:34 PM Scott Frey via Higher Education User Group <Mail@heug.org> wrote:

Good afternoon Lee, I don't see anything super-obvious, but some of the fields you're using do involve A TON of "transition" operators. So I...

PS Query & SQL

Post New Discussion

Re: Union Query

Reply to Thread

Jul 8, 2026 6:33 PM

Scott Frey

Good afternoon Lee,

I don't see anything super-obvious, but some of the fields you're using do involve A TON of "transition" operators. So I'm wondering if you have a field type/field width mismatch? Also, you have a SELECT DISTINCT statement in your new Union syntax but have simple SELECT statements in the other two levels.

What is the error you are seeing? Is it ORA-01790?

If you have access to the Alliance26 sessions, I recommend reviewing my sessions on creating Query Unions, 5823 & 5824, available here: www.heug.org/...

------------------------------
Scott Frey
Senior Data Analyst-HR Info Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Reply to Community Reply to Sender via Email View Thread Recommend

-------------------------------------------
Original Message:
Sent: 07-08-2026 06:01 PM

You are receiving this notification because you followed the 'Union Query' message thread. If you do not wish to follow this, please click here.
Update your email preferences to choose the types of email you receive
Unsubscribe from all participation emails

Original Message

4. RE: Union Query

Recommend

Jason Moyer

AI Innovator

Posted 4 days ago

Hi Lee,

Not sure how much help this will be but maybe one step closer. I believe a Union can only have a single Group By at the end. In this version I did that and caught an error on the Regex. Commenting out those, the query runs.

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 = '5002546687'
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)) ))
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 = '5002546687'
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 ))
UNION
SELECT O.EMPLID, CASE
WHEN O.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN O.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE S.FIRST_NAME
END, R.DESCR
--,regexp_replace(ListAgg (( TO_CHAR(CAST((O.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')), ',' ON OVERFLOW TRUNCATE '...' WITH COUNT)
--within group (order by TO_CHAR(CAST((O.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')
FROM (PS_EXT_COURSE O LEFT OUTER JOIN PS_NAMES_FERPA_VW S ON O.LASTUPDOPRID = S.EMPLID ), PS_EXT_ORG_TBL R
WHERE ( O.INSTITUTION = 'TMCC1'
AND O.TERM_YEAR > 2025
AND O.LASTUPDDTTM = (SELECT MAX( P.POST_DT+1)
FROM PS_TRNS_CRSE_TERM P, PS_TRNS_CRSE_SCH Q
WHERE P.EMPLID = O.EMPLID
AND P.INSTITUTION = O.INSTITUTION
AND P.MODEL_STATUS = 'P'
AND P.EMPLID = Q.EMPLID
AND P.ACAD_CAREER = Q.ACAD_CAREER
AND P.INSTITUTION = Q.INSTITUTION
AND P.MODEL_NBR = Q.MODEL_NBR
AND Q.EXT_ORG_ID = O.EXT_ORG_ID)
AND O.EXT_ORG_ID = R.EXT_ORG_ID
AND R.EFFDT =
(SELECT MAX(R_ED.EFFDT) FROM PS_EXT_ORG_TBL R_ED
WHERE R.EXT_ORG_ID = R_ED.EXT_ORG_ID
AND R_ED.EFFDT <= SYSDATE))
GROUP BY O.EMPLID, CASE
WHEN O.LASTUPDOPRID LIKE 'PStoPS_%'
THEN 'EDI'
WHEN O.LASTUPDOPRID = 'SCS_INTF_RAPTOR'
THEN 'Raptor'
ELSE S.FIRST_NAME
END, R.DESCR
ORDER BY 3

------------------------------
Jason Moyer
Digital Project Manager | PMP | SME| Higher Ed Digital
Huron Consulting Group
------------------------------

Original Message

5. RE: Union Query

Recommend

Jared Jakeman

Posted 4 days ago

Good morning, Lee,

Your screenshots of the fields tab from query manager show that only the first 10 characters of your fourth column are included in the output, and the dates are sorted within the listagg by themselves.

To simply your SQL statements, you could replace your expressions that combine regexp_replace and ListAgg with min.

Replace expression

regexp_replace(ListAgg (H.LASTUPDDTTM, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) within group (order by H.LASTUPDDTTM),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')

with expression

min(H.LASTUPDDTTM)

Note 1: Since your expression has a character length of 10, you may want to wrap the expression with SUBSTR(..., 1, 10).
Note 2: The output fields for all three SELECT statements only contain fields found in either PS_EXT_COURSE or PS_NAMES_FERPA_VW (the A.EMPLID at the top could just as easily be B.EMPLID), so the query could be rewritten as one SELECT statement with a more complex WHERE block.

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

Original Message

6. RE: Union Query

Recommend
Scott Frey
Posted 5 days ago
Hmmm...

Usually, an ORA-01789 is the result of not having the same number of fields on all Union levels. But in your examples, it looks like you have four fields on each level. I'll try to take a deeper look into your script tomorrow. It's already 5:00 PM here in Colorado.

------------------------------
Scott Frey
Connected Campus CoP Member - Senior Data Analyst-HR Info Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Original Message
7. RE: Union Query

Recommend
Andy Roberts
Posted 4 days ago
I don't know if this is related as it doesn't really show up in the SQL, but I know some things like Drilling URL's will throw this error if you don't include them in the unions as well. I'm unsure if translations or any other things (as I don't typically use those) can also cause it, but thought I'd mention it just in case, as I knew the first time that happened to me, it took a while to actually figure out what the problem was.

------------------------------
Andy Roberts
Business Analyst
State of Minnesota
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Original Message

PS Query & SQL

Union Query

Lee Raubolt5 days ago

Scott Frey5 days ago

Lee Raubolt5 days ago

Jason Moyer4 days ago

Jared Jakeman4 days ago

Scott Frey5 days ago

Andy Roberts4 days ago

1. Union Query

2. RE: Union Query

3. RE: Union Query

PS Query & SQL

4. RE: Union Query

PS Query & SQL

5. RE: Union Query

PS Query & SQL

6. RE: Union Query

7. RE: Union Query

HEUG is more than users—we're problem solvers.

Contact

Community

Events

Legal

Contact

Legal

PS Query & SQL

Union Query

Lee Raubolt5 days ago

Scott Frey5 days ago

Lee Raubolt5 days ago

Jason Moyer4 days ago

Jared Jakeman4 days ago

Scott Frey5 days ago

Andy Roberts4 days ago

1. Union Query

2. RE: Union Query

3. RE: Union Query

4. RE: Union Query

5. RE: Union Query

6. RE: Union Query

7. RE: Union Query

Related Content

HEUG is more than users—we're problem solvers.

Contact

Community

Events

Legal

Contact

Legal