sql – String concatenation result is too long

Question:

I made a select with the LISTAGG function to concatenate a field from my table, but the error is returned:

ORA-01489: string concatenation result is too long

query

   (SELECT LISTAGG (pf.pfnomereduzido || ' - ') within
   GROUP (
          ORDER BY pf.pfid)
   FROM fiscal cf
   LEFT JOIN pessoa pf ON pf.pfid = cf.contratofiscalpfid
   AND cf.contratofiscaldatafinal = '01/01/0001') AS contratofiscalnome

How can I solve?

Answer:

It has a limit in fact, treating distinct and reducing the size sometimes resolves,

try

 SELECT LISTAGG (V.pfnomereduzido || ' - ') within
   GROUP (
          ORDER BY pf.pfid)
   FROM (SELECT DISTINCT TRIM(pf.pfnomereduzido) 
         FROM fiscal cf
         LEFT JOIN pessoa pf ON pf.pfid = cf.contratofiscalpfid
         AND cf.contratofiscaldatafinal = '01/01/0001') V
Scroll to Top
AllEscort