I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do.
CREATE TABLE SPD_OWNER.EMP (
EMPID NUMBER,
NAME VARCHAR2(20),
MGRID NUMBER,
DEPT VARCHAR2(5)
);
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');
-- query which has issue
WITH tmp (rnum,dept,id) AS
(SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
(SELECT rnum, dept, id FROM tmp WHERE rnum=1
UNION ALL
SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
Am expecting below output.
5 IT 1/2/3/4/5
But running above query gives me an error SQL Error [25153] [99999]: ORA-25153: Temporary Tablespace is Empty
Isn't the condition tmp.rnum=cte.rnum+1 sufficient to break the recursive loop? Here's what am imagining to be happening.
- 1st select (above union all) will select rnum=1
- 1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e. tmp.rnum=2
- 2nd recursive iteration will select tmp.rnum=3
- and so on till this condition is invalid, i.e. when cte.rnum=5 (because then tmp.rum=5+1 will not have matching record)
Am assuming that am getting the error since it's going into infinite loop. What am i missing?
Side Note: Am using Oracle DB, so i can easily do this using LISTAGG. But i also want to run this query in H2 DB. So i want to have a solution that uses standard sql. Am open to other ways to achieve the same result.
listagg()
is in fact part of the SQL standard: modern-sql.com/feature/listagg