不能从内部查询外部引用ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY。尝试：

SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL 
      WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY =  ZONE 
      ORDER BY CODE)
GROUP BY TASK_CARD

For anyone that is still using wm_CONCAT (a.k.a. older db versions): The solution is to add distinct condition, it will then also apply ascending order to the concatenated values.

不要问为什么它没有被记录下来，但它会起作用的。

此外，在wm_concat之前的子查询中使用order by只会随机化顺序，因此不应该推荐使用它。

请求的SQL示例：

SELECT TASK_CARD, WM_CONCAT(distinct code) as ZONES
FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY =  ZONE 
GROUP BY TASK_CARD;

请注意，在程序/包中使用不同选项时不起作用。

如果给from子句中的子查询一个名称，则可以引用子查询本身中的列

SELECT t1.TASK_CARD
, WM_CONCAT(t1.code) as ZONES
FROM 
(SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL ORDER BY CODE) t1
WHERE t1.CONTROL_CATEGORY =  ZONE 
GROUP BY t1.TASK_CARD

1. Order by the desired column, then
2. Order in external query order by row number.
3. Use the function.

此函数具有最后一个行号顺序的逻辑：

Select wmsys.wm_concat(t) CONCAT from 
(
    Select t from (
        Select t from (
            Select  aa  t from dual
            union
            Select  zz  t from dual
            union
            Select  pp  t from dual
            union
            Select  ll  t from dual
            union
            Select  mm  t from dual
            union
            Select  xx  t from dual
            union
            Select  cc  t from dual
        ) a 
        order by t
    ) order by rownum
) t

LISTAGG是在11g Release 2中引入的。

因此，在11g之前的Oracle版本中，LISTAGG不受支持，可以使用ROW_NUMBER（）和SYS_CONNECT_BY_PATH函数。

请参阅Oracle字符串聚合技术

SELECT task_card,
  LTRIM(MAX(SYS_CONNECT_BY_PATH(code, , ))
  KEEP (DENSE_RANK LAST ORDER BY curr), , ) AS zones
  FROM   (SELECT task_card,
                code,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) AS curr,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) -1 AS prev
         FROM   table_name)
  GROUP BY task_card
  CONNECT BY prev = PRIOR curr AND task_card= PRIOR task_card
 START WITH curr = 1;

注意

切勿使用WM_CONCAT，因为它是一个未记录的功能，并且已从12c版本中删除。

任何一直依赖wm_concat函数的应用程序在升级到12c后将无法工作。自，它已被删除。请参阅为什么不在Oracle中使用WM_CONCAT函数

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner= WMSYS 
  4  AND object_name LIKE  WM\_%  ESCAPE   ;

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

SQL>

您将收到一个“无效标识符”错误：

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

因此，依赖未记录的功能是没有意义的，因为它在最新版本中不再可用。

使用ListAgg而不是wm_concat

SELECT TASK_CARD, ListAgg(code) within (order by code asc) as ZONES

http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html