PS Query & SQL

I'm working modifying a courses last offered query by adding the term description from the TERM_VAL_TBL. Unfortunately, this isn't working as expected because I'm receiving an 'Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)' message. I think this is because CLASS_TBL is looking for the maximum STRM - when I remove the criteria it runs successfully, but for every term. Any suggestions for how I can resolve this error?

Here's my SQL if it is helpful:
    
SELECT DISTINCT A.CRSE_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), B.CATALOG_PRINT, B.SUBJECT, B.CATALOG_NBR, A.DESCR, B.SSR_CRSE_TYPOFF_CD, D.DESCR, MAX( C.STRM)
  FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN  PS_CLASS_TBL C ON  B.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR ), PS_SSR_CRSE_TYPOFF D, PS_TERM_VAL_TBL E
  WHERE ( D.SSR_CRSE_TYPOFF_CD = B.SSR_CRSE_TYPOFF_CD
    AND D.INSTITUTION = B.INSTITUTION
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
        WHERE A.CRSE_ID = A_ED.CRSE_ID)
     AND A.CRSE_ID = B.CRSE_ID
     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 D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_SSR_CRSE_TYPOFF D_ED
        WHERE D.INSTITUTION = D_ED.INSTITUTION
          AND D.SSR_CRSE_TYPOFF_CD = D_ED.SSR_CRSE_TYPOFF_CD
          AND D_ED.EFFDT <= B.EFFDT)
     AND MAX( C.STRM) = E.STRM ))
  GROUP BY  A.CRSE_ID,  A.EFFDT,  B.CATALOG_PRINT,  B.SUBJECT,  B.CATALOG_NBR,  A.DESCR,  B.SSR_CRSE_TYPOFF_CD,  D.DESCR
  ORDER BY 1

Original Message:
Sent: 1/29/2026 11:35:00 AM
From: Pheng Xiong
Subject: Can't Join TERM_VAL_TBL

I'm working modifying a courses last offered query by adding the term description from the TERM_VAL_TBL. Unfortunately, this isn't working as expected because I'm receiving an 'Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)' message. I think this is because CLASS_TBL is looking for the maximum STRM - when I remove the criteria it runs successfully, but for every term. Any suggestions for how I can resolve this error?

Here's my SQL if it is helpful:
    
SELECT DISTINCT A.CRSE_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), B.CATALOG_PRINT, B.SUBJECT, B.CATALOG_NBR, A.DESCR, B.SSR_CRSE_TYPOFF_CD, D.DESCR, MAX( C.STRM)
  FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN  PS_CLASS_TBL C ON  B.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR ), PS_SSR_CRSE_TYPOFF D, PS_TERM_VAL_TBL E
  WHERE ( D.SSR_CRSE_TYPOFF_CD = B.SSR_CRSE_TYPOFF_CD
    AND D.INSTITUTION = B.INSTITUTION
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
        WHERE A.CRSE_ID = A_ED.CRSE_ID)
     AND A.CRSE_ID = B.CRSE_ID
     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 D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_SSR_CRSE_TYPOFF D_ED
        WHERE D.INSTITUTION = D_ED.INSTITUTION
          AND D.SSR_CRSE_TYPOFF_CD = D_ED.SSR_CRSE_TYPOFF_CD
          AND D_ED.EFFDT <= B.EFFDT)
     AND MAX( C.STRM) = E.STRM ))
  GROUP BY  A.CRSE_ID,  A.EFFDT,  B.CATALOG_PRINT,  B.SUBJECT,  B.CATALOG_NBR,  A.DESCR,  B.SSR_CRSE_TYPOFF_CD,  D.DESCR
  ORDER BY 1

------------------------------
Pheng Xiong
Senior Associate Registrar
Rochester Institute of Technology
------------------------------

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

Thanks Vic. I initially had it set as an expression as you noted, but the issue is that the term table will display the description for every term the class was offered, ignoring the expression.

I'm working modifying a courses last offered query by adding the term description from the TERM_VAL_TBL. Unfortunately, this isn't working as expected because I'm receiving an 'Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)' message. I think this is because CLASS_TBL is looking for the maximum STRM - when I remove the criteria it runs successfully, but for every term. Any suggestions for how I can resolve this error?

Here's my SQL if it is helpful:
    
SELECT DISTINCT A.CRSE_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), B.CATALOG_PRINT, B.SUBJECT, B.CATALOG_NBR, A.DESCR, B.SSR_CRSE_TYPOFF_CD, D.DESCR, MAX( C.STRM)
  FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN  PS_CLASS_TBL C ON  B.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR ), PS_SSR_CRSE_TYPOFF D, PS_TERM_VAL_TBL E
  WHERE ( D.SSR_CRSE_TYPOFF_CD = B.SSR_CRSE_TYPOFF_CD
    AND D.INSTITUTION = B.INSTITUTION
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
        WHERE A.CRSE_ID = A_ED.CRSE_ID)
     AND A.CRSE_ID = B.CRSE_ID
     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 D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_SSR_CRSE_TYPOFF D_ED
        WHERE D.INSTITUTION = D_ED.INSTITUTION
          AND D.SSR_CRSE_TYPOFF_CD = D_ED.SSR_CRSE_TYPOFF_CD
          AND D_ED.EFFDT <= B.EFFDT)
     AND MAX( C.STRM) = E.STRM ))
  GROUP BY  A.CRSE_ID,  A.EFFDT,  B.CATALOG_PRINT,  B.SUBJECT,  B.CATALOG_NBR,  A.DESCR,  B.SSR_CRSE_TYPOFF_CD,  D.DESCR
  ORDER BY 1

Hello Pheng,

You may want try making STRM equal to a subquery where you select STRM and make it the max value in the subquery.  The SQL would look similar to the following (I am using a different table):

WHERE A.STRM = (SELECT MAX( B.STRM)
  FROM PS_FAN_FA_TERM_VW B
  WHERE B.EMPLID = A.EMPLID
     AND B.INSTITUTION = A.INSTITUTION)

Let me know if screenshots would help more.

Thanks Vic. I initially had it set as an expression as you noted, but the issue is that the term table will display the description for every term the class was offered, ignoring the expression.

I'm working modifying a courses last offered query by adding the term description from the TERM_VAL_TBL. Unfortunately, this isn't working as expected because I'm receiving an 'Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)' message. I think this is because CLASS_TBL is looking for the maximum STRM - when I remove the criteria it runs successfully, but for every term. Any suggestions for how I can resolve this error?

Here's my SQL if it is helpful:
    
SELECT DISTINCT A.CRSE_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), B.CATALOG_PRINT, B.SUBJECT, B.CATALOG_NBR, A.DESCR, B.SSR_CRSE_TYPOFF_CD, D.DESCR, MAX( C.STRM)
  FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN  PS_CLASS_TBL C ON  B.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR ), PS_SSR_CRSE_TYPOFF D, PS_TERM_VAL_TBL E
  WHERE ( D.SSR_CRSE_TYPOFF_CD = B.SSR_CRSE_TYPOFF_CD
    AND D.INSTITUTION = B.INSTITUTION
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
        WHERE A.CRSE_ID = A_ED.CRSE_ID)
     AND A.CRSE_ID = B.CRSE_ID
     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 D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_SSR_CRSE_TYPOFF D_ED
        WHERE D.INSTITUTION = D_ED.INSTITUTION
          AND D.SSR_CRSE_TYPOFF_CD = D_ED.SSR_CRSE_TYPOFF_CD
          AND D_ED.EFFDT <= B.EFFDT)
     AND MAX( C.STRM) = E.STRM ))
  GROUP BY  A.CRSE_ID,  A.EFFDT,  B.CATALOG_PRINT,  B.SUBJECT,  B.CATALOG_NBR,  A.DESCR,  B.SSR_CRSE_TYPOFF_CD,  D.DESCR
  ORDER BY 1

I'm working modifying a courses last offered query by adding the term description from the TERM_VAL_TBL. Unfortunately, this isn't working as expected because I'm receiving an 'Error in running query because of SQL Error, Code=934, Message=ORA-00934: group function is not allowed here (50,380)' message. I think this is because CLASS_TBL is looking for the maximum STRM - when I remove the criteria it runs successfully, but for every term. Any suggestions for how I can resolve this error?

Here's my SQL if it is helpful:
    
SELECT DISTINCT A.CRSE_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), B.CATALOG_PRINT, B.SUBJECT, B.CATALOG_NBR, A.DESCR, B.SSR_CRSE_TYPOFF_CD, D.DESCR, MAX( C.STRM)
  FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN  PS_CLASS_TBL C ON  B.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR ), PS_SSR_CRSE_TYPOFF D, PS_TERM_VAL_TBL E
  WHERE ( D.SSR_CRSE_TYPOFF_CD = B.SSR_CRSE_TYPOFF_CD
    AND D.INSTITUTION = B.INSTITUTION
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
        WHERE A.CRSE_ID = A_ED.CRSE_ID)
     AND A.CRSE_ID = B.CRSE_ID
     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 D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_SSR_CRSE_TYPOFF D_ED
        WHERE D.INSTITUTION = D_ED.INSTITUTION
          AND D.SSR_CRSE_TYPOFF_CD = D_ED.SSR_CRSE_TYPOFF_CD
          AND D_ED.EFFDT <= B.EFFDT)
     AND MAX( C.STRM) = E.STRM ))
  GROUP BY  A.CRSE_ID,  A.EFFDT,  B.CATALOG_PRINT,  B.SUBJECT,  B.CATALOG_NBR,  A.DESCR,  B.SSR_CRSE_TYPOFF_CD,  D.DESCR
  ORDER BY 1