Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rajat.

Asked: August 06, 2010 - 4:37 am UTC

Last updated: August 07, 2010 - 8:30 am UTC

Version: 9.1

Viewed 1000+ times

You Asked

Hi,

I am trying to do string aggregation in Oracle 9.1 (through TOAD 9.1) using SYS_CONNECT_BY_PATH. I will use the example as given at williamrobertson.net to explain my issue:

SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp
)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

The code in italics works fine but when I run the entire query I get an error:

deptno: invalid identifier.

I have to achieve string aggregation. There are plenty of rows in my table with same deptno and I have to concatenate the values of ename and group it by deptno. This was I will have only one single value (concatenated field of ename) against each deptno.

Any suggestions?

P.S.: I am using complete names for deptno and ename : schema name.table name.column name.

and Tom said...

you've slightly overdone that - made it more complicated than necessary. I never tried your query, I just plugged in my template query for this and it goes right off:

ops$tkyte%ORA9IR2> SELECT deptno, max(sys_connect_by_path(ename,','))
  2  FROM  (SELECT deptno,
  3            ename,
  4            ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
  5      FROM  scott.emp)
  6  GROUP BY deptno
  7  CONNECT BY rn = PRIOR rn+1 AND deptno = PRIOR deptno
  8  START WITH rn = 1;

    DEPTNO
----------
MAX(SYS_CONNECT_BY_PATH(ENAME,','))
-------------------------------------------------------------------------------
        10
,CLARK,KING,MILLER

        20
,ADAMS,FORD,JONES,SCOTT,SMITH

        30
,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD



the keey dense rank stuff - not at all necessary nor desired.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MAX not needed

Donat Callens, August 11, 2010 - 4:01 am UTC

You can use this version also. It replaces the MAX trick with CONNECT_BY_ISLEAF.
SELECT deptno,
sys_connect_by_path (ename, ',')
FROM
(SELECT deptno,
ename,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
WHERE connect_by_isleaf = 1
CONNECT BY rn = PRIOR rn + 1
AND deptno = PRIOR deptno
START WITH rn = 1;

The result is the same. But I find it more intuitive to read.

Excellent point

A reader, August 16, 2010 - 6:02 pm UTC

I'm sure I thought there was a good reason for the DENSE_RANK LAST construction when I posted that in 2004, but you're quite right, it's not needed and neither is the second row_number() expression.

Both versions work fine for me in 11.1 though, and the use of deptno is unchanged, so I would be interested to hear whether the OP still gets the "invalid identifier" error. Perhaps there is a clue in the "PS".

I like connect_by_isleaf as well - thanks Donat!

connect_by_isleaf

Dennis Schnell, April 12, 2012 - 6:18 am UTC

Hi I tested also the second statement, but it doesn't work on Oracle 9i.

If you try to use the "connect_by_isleaf" in the where clause you will get the error message: ORA-00904: "CONNECT_BY_ISLEAF": invalid identifier

This works perfect on 10g, but not on 9i - just to keep it in your mind ;-)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.