0

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.

  1. 1st select (above union all) will select rnum=1
  2. 1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e. tmp.rnum=2
  3. 2nd recursive iteration will select tmp.rnum=3
  4. 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.

7
  • 1
    Nitpicking: listagg() is in fact part of the SQL standard: modern-sql.com/feature/listagg
    – user1822
    Mar 26, 2019 at 7:51
  • @Akina, i don't follow your recommendation. could you pls show me the modified query? Mar 26, 2019 at 7:56
  • @a_horse_with_no_name, i will try this with H2 and provide an update in sometime. Mar 26, 2019 at 7:56
  • @Akina, am still getting the same error. I had tried to remove the select max(rnum) part from my original query. The error was still there, which led me to think that issue is with infinite loop happening with the cte Mar 26, 2019 at 8:02
  • 1
    OT: why do you need H2? Just to run tests? Recursive CTEs might scale badly, use the simplest solution.
    – ibre5041
    Mar 26, 2019 at 8:36

1 Answer 1

1

That error does not mean the temp tablespace got full, it means it is empty. Empty as no tempfile in it.

CREATE TABLE 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:

SQL> 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
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

      RNUM DEPT  ID
---------- ----- --------------------
         5 IT    1/2/3/4/5

Now if I drop the tempfile:

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> 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
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
WITH tmp (rnum,dept,id) AS
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

Check DBA_TEMP_FILES and fix your temporary tablespace.

1
  • Thank you. I was going insane for a minute there. I tried the same query in another DB env and it worked there. It also worked on H2. So it looks like an issue with a specific DB (DEV) that I was playing with. Mar 26, 2019 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.