sql - Oracle Listagg 子查询

Question

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';

它抛出错误为

ORA-01722: invalid number

01722. 00000 - "invalid number"

*Cause: The specified number was invalid.

*Action: Specify a valid number.

Answer 1

我假设您想要这样的查询:

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' ;

注释:

添加了 SELECT之前 LISTAGG() .子查询需要。

删除 GROUP BY .不需要，因为您希望子查询始终返回一行。

从可能是数字的常量中删除了单引号。仅对字符串和日期常量使用单引号。

向子查询添加了相关子句。

将列的名称移到子查询之外，因此它在结果中被命名。

添加了 ORDER BY列 WITHIN GROUP .不是必需的，但可以合理地保持 id 的顺序。