ORACLE/SQL: wm_concat & order by

Question

I'm using oracle 11 (not sure about the exact version, but since LISTAGG doesn't work, I suppose it's not release 2) through ODBC and crystal reports 2008.

Here is the problem I have:

Here's a table:

TABLE ODB.TASK_CARD_CONTROL  
------------------------------------------  
task_card     control_category     code  
------------------------------------------  
1                  zone             17  
1                  zone             33  
1                  zone             21  
2                  zone             18  
2                  zone             05  
3                  zone             55  
3                  zone             32  
3                  zone             72

I'm using the WM_CONCAT function to obtain something like this:

task_card      zones
1              17,33,21
2              18,05
3              55,32,72

Here is the SQL for that:

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

but I'd like the zones to be sorted, so I tried this:

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

but for some reason, it returns the following error:

Failed to retrieve data from the database.
Details: 42S22:[Oracle][ODBC][Ora]ORA-00904: 
"ODB"."TASK_CARD_CONTROL"."CONTROL_CATEGORY" : invalid identifier

I really don't understand what I'm doing wrong here... Can anybody give me a hint ?

marc_s · Accepted Answer · 2016-01-05 22:18:35Z

7

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.

Don't ask why it's not documented, but it will work.

Also, using a order by in a subquery, previous to wm_concat will just randomize the order, so it shouldn't have been recommended.

Example for the requested 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;

Just be warned that the distinct option does not work when used in procedures/packages .

edited Jan 5, 2016 at 22:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 21, 2014 at 12:15

Alexandru Simionov

711 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2011-02-11 15:11:35Z

2

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

answered Feb 11, 2011 at 15:11

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

3 Comments

Christian M Over a year ago

Well I thought about that in the meantime; I tried this : SELECT TASK_CARD, WM_CONCAT(code) as ZONES FROM (SELECT TASK_CARD, CODE, FROM ODB.TASK_CARD_CONTROL WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE' ORDER BY CODE) GROUP BY TASK_CARD - but the omission of CONTROL_CATEGORY in the select gave me an error. Added it back as in your query, and it seems to work fine now ! thanks :)

Tony Andrews Over a year ago

@LalitKumarB Why are you asking me? I was answering a question where WM_CONCAT was (incidentally) used, not advocating its use!

Tony Andrews Over a year ago

@LalitKumarB No problem!

Widor · Accepted Answer · 2012-05-28 13:55:43Z

0

If you give the sub query in the from clause a name you can then refer to columns in the sub query itself

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

edited May 28, 2012 at 13:55

Widor

13.3k7 gold badges47 silver badges67 bronze badges

answered May 28, 2012 at 13:46

Bren M

1

1 Comment

RichardTheKiwi Over a year ago

You can already do that even without a subquery alias as the accepted answer shows

4444 · Accepted Answer · 2015-08-26 19:22:09Z

0

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

This function has logic for the last rownum order:

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

edited Aug 26, 2015 at 19:22

4444

3,73010 gold badges35 silver badges47 bronze badges

answered Aug 26, 2015 at 18:52

Netflix Mumbhai

1

Comments

Lalit Kumar B · Accepted Answer · 2016-04-10 11:08:18Z

LISTAGG was introduced in 11g Release 2.

Therefore, in Oracle version prior to 11g where LISTAGG is not supported, you could use ROW_NUMBER() and SYS_CONNECT_BY_PATH functions.

See Oracle String Aggregation Techniques

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;

NOTE

Never use WM_CONCAT since it is an undocumented feature and it has been removed from 12c version.

Any application which has had been relying on wm_concat function will not work once upgraded to 12c. Since, it has been removed. See Why not use WM_CONCAT function in Oracle?

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>

You will receive an “invalid identifier” error:

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

Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

Kyle Hu · Accepted Answer · 2012-03-16 08:41:05Z

-1

Use ListAgg instead of 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

answered Mar 16, 2012 at 8:41

Kyle Hu

11 bronze badge

1 Comment

Ben Over a year ago

The OP clearly stated that he couldn't use listagg. Also, it's WITHIN GROUP.

Collectives™ on Stack Overflow

ORACLE/SQL: wm_concat & order by

6 Answers 6

Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related