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
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
How to concatenate multiple rows order by sequence in Oracle10g
Never use WM_CONCAT
. Read Why not use WM_CONCAT function in Oracle?
See this topic https://stackoverflow.com/a/28758117/3989608.
It is undocumented, and any application which relies on WM_CONCAT
will not work once upgraded to 12c
because it has been removed from the latest 12c version.
There are many ways of doing string-aggregation, depending on the database version. See few examples below:
11gR2
Use LIASTAGG
:
SQL> SELECT grp,
2 listagg(command, ',') WITHIN GROUP(
3 ORDER BY seq) command
4 FROM t
5 GROUP BY grp;
GRP COMMAND
--- --------------------------------------------------------------------------------------------
ONE <?xml version=1.0 encoding=UTF-8?>,<message1>MESSAGE</message1>,<message2>MESSAGE</message2>
TWO <?xml version=1.0 encoding=UTF-8?>,<message2>MESSAGE</message2>,<message9>MESSAGE</message9>
SQL>
9i and up
Use ROW_NUMBER()
and SYS_CONNECT_BY_PATH
:
SQL> SELECT grp,
2 LTRIM(MAX(SYS_CONNECT_BY_PATH(command,','))
3 KEEP (DENSE_RANK LAST ORDER BY seq),',') command
4 FROM (SELECT grp,
5 command,
6 seq,
7 ROW_NUMBER() OVER (PARTITION BY grp ORDER BY seq) AS curr,
8 ROW_NUMBER() OVER (PARTITION BY grp ORDER BY seq) -1 AS prev
9 FROM t)
10 GROUP BY grp
11 CONNECT BY prev = PRIOR curr AND grp = PRIOR grp
12 START WITH curr = 1;
GRP COMMAND
--- --------------------------------------------------------------------------------------------
ONE <?xml version=1.0 encoding=UTF-8?>,<message1>MESSAGE</message1>,<message2>MESSAGE</message2>
TWO <?xml version=1.0 encoding=UTF-8?>,<message2>MESSAGE</message2>,<message9>MESSAGE</message9>
SQL>
Concatenate Over Oracle
OP is on Oracle 10g, and 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.
SELECT fruit,
LTRIM(MAX(SYS_CONNECT_BY_PATH(number,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
FROM (SELECT fruit,
number,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) -1 AS prev
FROM table_name)
GROUP BY fruit
CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit
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.
Oracle: stragg and wm_concat return data in the incorrect order
stragg
is a user-defined aggregate function. Various folks (Tom Kyte, Tim Hall, and many others) have written various versions of that function. Not knowing which of these functions you started with or what customizations you made to it, I'll have to speculate a bit. In general, though, those functions are not doing any sort of internal sort so the order that values appear in the list is arbitrary. It would be perfectly valid for the function to return one order today and a different order tomorrow or for the order to be different in different sessions.
Various people have also written user-defined aggregate functions that return lists of data in a sorted order. For example, Gary Myers has a variant of the stragg
function that sorts the elements alphabetically. You could use that and customize the function to sort the results by whatever you'd like to sort them by.
If you are using Oracle 11.2 or later, though, you'd be much better off using the LISTAGG
analytic function which makes it much easier to specify (or change) the way you are sorting data.
Two wm_concat in a single query
You could either use SYS_CONNECT_BY_PATH or XMLAGG as alternatives to WM_CONCAT (unsupported). An example for SYS_CONNECT_BY_PATH is found in an Oracle Forum discussion here. I created a SQL Fiddle using XMLAGG here.
Also, you can use the ORDER BY clause (as shown in the examples), if you need the values concatenated in a certain order.
References:
LISTAGG alternative in 10g
Related Question on SO
Related Topics
Mysql: Searching Between Dates Stored as Varchar
Can SQL Clr Triggers Do This? or Is There a Better Way
Bigquery SQL: Average, Geometric Mean, Remove Outliers, Median
SQL Server 2019 Installation Windows 11 "Wait on the Database Engine Recovery Handle Failed" Error
How to Make This Query in SQL Server Compact Edition
SQL Server Automatic Update Datetimestamp Field
Select Non-Empty Columns Using SQL Server
Renaming a Column Without Breaking the Scripts and Stored Procedures
Split Ipv4 Address into 4 Numbers in Oracle SQL
Eliminate Duplicates Using Oracle Listagg Function
How to Return a New Identity Column Value from an SQLserver Select Statement
SQL Count to Include Zero Values
Can Scalar Functions Be Applied Before Filtering When Executing a SQL Statement
SQL Query to Create a Calculated Field
How to Parse Xml Tags in Bigquery Standard SQL
To Get Date from Datetime in SQL