Oracle Listagg子查询

Question

Oracle Listagg子查询

4

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: 无效数字

01722. 00000 - "无效数字"

*原因：指定的数字无效。

*操作：请指定一个有效的数字。

- Emperor

我假设你的查询中实际上并没有使用反引号。 - Gordon Linoff

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

我假设你想要像这样的查询：

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

评论:

在LISTAGG()之前添加了SELECT。需要用于子查询。
删除了GROUP BY。不需要，因为您希望子查询始终返回一行。
从常量中删除了单引号，这些常量可能是数字。只对字符串和日期常量使用单引号。
向子查询添加了相关子句。
将列名移出子查询，以便在结果中命名该列。
为WITHIN GROUP添加了一个ORDER BY列。不是必要的，但保持id顺序是合理的。