Thursday, November 19, 2015

LISTAGG function giving extra spaces in result

Lets reproduce the error:

create table emp1 as select * from emp;

alter table emp modify ename nvarchar2(20);


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

    DEPTNO EMPLOYEES
---------- --------------------------------------------------------------------------
        10  C L A R K, K I N G, M I L L E R
        20  A D A M S, F O R D, J O N E S, S C O T T, S M I T H
        30  A L L E N, B L A K E, J A M E S, M A R T I N, T U R N E R, W A R D

CAUSE
LISTAGG function returns varchar2, if we execute query on column of type nvarchar2 then value will startimplicit conversion to varchar2.

SOLUTION
use LISTAGG function always on column of type VARCHAR.


No comments:

Post a Comment

web stats