The result of the string concatenation is too long

Asked

Viewed 2,389 times

3

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

ORA-01489: the result of string concatenation 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?

sql query oracle
  • 1

    I don’t know Oracle, but maybe this example can help you: LISTAGG Function: "result of string concatenation is Too long"

  • Try something like: (SELECT RTRIM(XMLAGG(XMLELEMENT(E,pf.pfnomereduzido,',').EXTRACT('//text()') ORDER BY pf.pfnomereduzido).GetClobVal(),',') 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

  • now the error has changed: FROM keyword not located where expected with the error pointed to the Within GROUP part, must be syntax error...

  • Take this error as a hint that what you’re doing should be done on the client side, not directly in SQL.

  • The solution is the path that Marconi posted ... already solved problems as he suggested..

1 answer

1

It has a de facto limit, treat distinct and reduce 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

  • would not have to have a closure ) after the V?

  • I think so, I have no way to test

  • this way appears: Unexpected end of SQL command

Browser other questions tagged sql query oracle

You are not signed in. Login or sign up in order to post.