sql - LISTAGG in ORACLE -
i trying use listagg() fetch more 2 columns.
select deptname, deptno, listagg(ename, ',') within group (order ename) employees emp group deptno;
but throwing error:
: keyword not found expected 00000 - "from keyword not found expected" *cause: *action: error @ line: 3 column: 12
can please explain why is?
the listagg analytic function introduced in oracle 11g release 2. so, if on older version, won't able use it.
the error seems strange. should ora-00904: "deptname": invalid identifier
standard emp
table in scott
schema doesn't have deptname column. also, should ora-00979: not group expression
did not mention selected columns in group by expression.
using standard emp table in scott schema:
sql> select deptno, 2 job, 3 listagg(ename, ',') within group ( 4 order ename) employees 5 emp 6 group 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>
Comments
Post a Comment