Emperor Emperor - 29 days ago 12
SQL Question

Oracle Listagg Sub query

SELECT e.pem_id AS id,
e.pem_subject AS subject,
e.pem_content AS content,
e.pem_sent_date AS sentdate,
e.pem_ref_id AS referenceid,
e.pem_from_usr_id AS userid,
NULL AS misc,
(listagg(str.str_us_id, ', ') within GROUP(ORDER BY '') AS attachedusers FROM
proj_email_usrs eu LEFT OUTER JOIN st_register str ON
eu.pmu_str_id = str.str_id WHERE eu.pmu_pem_id = '26' GROUP BY '')
FROM proj_email e
WHERE e.pem_prj_id = '33'
AND e.pmu_pem_id = '26'
AND e.pem_status = 'S';


It is throwing error as


ORA-01722: invalid number

01722. 00000 - "invalid number"

*Cause: The specified number was invalid.

*Action: Specify a valid number.

Answer

I assume you want a query like this:

SELECT E.PEM_ID as Id, E.PEM_SUBJECT as Subject, E.PEM_CONTENT as Content,
       E.PEM_SENT_DATE as SentDate, E.PEM_REF_ID as ReferenceId,   
       E.PEM_FROM_USR_ID as UserId, NULL as Misc,
       (SELECT LISTAGG(STR.STR_US_ID, ', ') WITHIN GROUP (ORDER BY STR.STR_US_ID)
        FROM PROJ_EMAIL_USRS EU LEFT OUTER JOIN
             ST_REGISTER STR
             ON EU.PMU_STR_ID = STR.STR_ID
        WHERE EU.PMU_PEM_ID = E.PMU_PEM_ID  -- Correlation clause
       ) as AttachedUsers
FROM PROJ_EMAIL E 
WHERE E.PEM_PRJ_ID = 33 AND E.PMU_PEM_ID = 26 AND E.PEM_STATUS = 'S' ;

Comments:

  • Added the SELECT before LISTAGG(). Needed for a subquery.
  • Remove GROUP BY. Not needed, because you want the subquery to always return one row.
  • Removed single quotes from constants that are probably numbers. Only use single quotes for string and date constants.
  • Added a correlation clause to the subquery.
  • Moved the name of the column outside the subquery, so it is named in the result.
  • Added an ORDER BY column for WITHIN GROUP. Not necessary, but reasonable to keep the ids in order.