CODEWITHSUNDEEP
sqloracle-databaselistagg
Dream
Dream

Reputation: 43

LISTAGG in ORACLE

I am trying to use LISTAGG() to fetch more than two columns.

SELECT deptname, deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees 
FROM emp 
GROUP BY deptno;

But it is throwing this error:

: FROM keyword not found where expected
 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 3 Column: 12

Can please somebody explain why it is?

Upvotes: 3

Views: 2126

Answers (3)

cuirw
cuirw

Reputation: 1

Oracle 11g: 

SELECT deptname, deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY deptno,job) AS employees 
FROM emp 
GROUP BY deptno,job;

Oracle 10g: 

SELECT deptname, deptno, WMSYS.WM_CONCAT(ename) AS employees 
FROM emp 
GROUP BY deptno,job;

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The LISTAGG analytic function was introduced in Oracle 11g Release 2. So, if you are on older version, you won't be able to use it.

The error seems strange. You should actually get ORA-00904: "DEPTNAME": invalid identifier as the standard EMP table in SCOTT schema doesn't have DEPTNAME column. Also, you should get ORA-00979: not a GROUP BY expression as you did not mention the SELECTed columns in the GROUP BY expression.

Using the standard EMP table in SCOTT schema:

SQL> SELECT deptno,
  2    job,
  3    LISTAGG(ename, ',') WITHIN GROUP (
  4  ORDER BY ename) AS employees
  5  FROM emp
  6  GROUP BY deptno,
  7    job;

    DEPTNO JOB       EMPLOYEES
---------- --------- ------------------------
        10 CLERK     MILLER
        10 MANAGER   CLARK
        10 PRESIDENT KING
        20 CLERK     ADAMS,SMITH
        20 ANALYST   FORD,SCOTT
        20 MANAGER   JONES
        30 CLERK     JAMES
        30 MANAGER   BLAKE
        30 SALESMAN  ALLEN,MARTIN,TURNER,WARD

9 rows selected.

SQL>

Upvotes: 3

Haytem BrB
Haytem BrB

Reputation: 1499

Try:

SELECT deptname, deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees 
FROM emp 
GROUP BY deptno,deptname;

Upvotes: 2

Related Questions