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 ?

Answer 1

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 .

Answer 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