Why Does the Wm_Concat Not Work Here

ORACLE/SQL: wm_concat & order by

You can't reference ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY from outside the inner query. Try:

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

How can I replace the WM_CONCAT function in this query? [ORA-00904: WM_CONCAT: invalid identifier]

We use LISTAGG nowadays, e.g.

SQL> select deptno,
  2         listagg(ename, ',') within group (order by ename) list_of_employees
  3  from emp
  4  group by deptno;

    DEPTNO LIST_OF_EMPLOYEES
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

wm_concat was undocumented, you shouldn't have used it anyway.

Comma separated column without XML path

Since you are on Oracle 10g version, you cannot use LISTAGG. It was introduced in 11g.

And please DON'T use WM_CONCAT as it is an undocumented feature, and has been removed from the latest release. See Why does the wm_concat not work here?

For 10g, you have following string aggregation techniques:

1. ROW_NUMBER() and SYS_CONNECT_BY_PATH
2. User-defined function STRAGG as demonstrated by Tom Kyte here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
3. COLLECT function in PL/SQL

Following is a pure SQL method using ROW_NUMBER() and SYS_CONNECT_BY_PATH functions available since 9i:

SQL> column emp_list format a50
SQL> SELECT deptno,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
  3         KEEP (DENSE_RANK LAST ORDER BY cur),',') AS emp_list
  4  FROM   (SELECT deptno,
  5                 ename,
  6                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS cur,
  7                 ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
  8          FROM   emp)
  9  GROUP BY deptno
 10  CONNECT BY prev = PRIOR cur AND deptno = PRIOR deptno
 11  START WITH cur = 1;

    DEPTNO EMP_LIST
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Change separator of WM_CONCAT function of Oracle 11gR2

You might want to use LISTAGG.

SELECT col_id, 
       LISTAGG(col_text, '|') WITHIN GROUP (ORDER BY col_text) text
  FROM table1
 GROUP BY col_id

Output:

| COL_ID |            TEXT |
----------------------------
|    111 | This|a|is|test. |

SQLFiddle

UPDATE If you need to get distinct text values in a list

SELECT col_id, 
       LISTAGG(col_text, '|')
         WITHIN GROUP (ORDER BY col_text) text
  FROM 
(
  SELECT DISTINCT col_id, col_text
    FROM table1
)
 GROUP BY col_id

SQLFiddle

WM_CONCAT duplicates removal

Firstly, never use WM_CONCAT since it is undocumented and is no more available in the latest version 12c. See Why not use WM_CONCAT function in Oracle? and Why does the wm_concat not work here?

Since you are on 11g, you could use LISTAGG.

The below query does following things:

1. Split the comma delimited string into rows.
2. Apply string aggregation using LISTAGG.
3. CASE expression to handle the custom ordering.

For example,

SQL> WITH DATA AS(
  2      SELECT 12345 colA, 'NHS,CDE,BCD' colB FROM dual UNION ALL
  3      SELECT 12345 colA, 'NHS,ABC,DEF' colB FROM dual
  4      )
  5  SELECT cola,
  6    listagg(colb, ',') WITHIN GROUP(
  7  ORDER BY
  8    CASE colb
  9      WHEN 'NHS'
 10      THEN 1
 11      ELSE 2
 12    END, colb) colb
 13  FROM
 14    (SELECT DISTINCT cola,
 15      trim(regexp_substr(colb, '[^,]+', 1, LEVEL)) colb
 16    FROM DATA
 17      CONNECT BY LEVEL <= regexp_count(colb, ',')+1
 18    ORDER BY colb
 19    )
 20  GROUP BY cola
 21  /

      COLA COLB
---------- ------------------------------
     12345 NHS,ABC,BCD,CDE,DEF

Edit As @AlexPoole pointed out, explicit ordering was missing and previous query(see edit history) relied on the distinct ordering of the values.

---

Related Topics