ListAgg errors out in OBIEE 11g

Question

Hi All,

I have a requirement of converting rows to column level and tried to implement using ListAgg function as shown below along with Regexp_replace to eliminate duplication of values.

But with ListAgg retrieves data , if  the record exceeds 4000 characters then report errors out.

Evaluate( 'regexp_replace(LISTAGG(%1,%2)  WITHIN GROUP ( ORDER BY (%1) )  OVER ( PARTITION BY (%3) ), ' ' ( [^,]+)(,[ ] *\1)+ ' ' , ' ' \1 ' ' ) ' as Varchar (4000), "Column1", ',' "Column2).

Can any one please suggest and help a workaround to achieve this ?

How can i use XMLAGG function ?

Please help.

Thanks,

LonaD

deeps · Answer

If you can, create a custom function on your database and you can use it in the reports. In PL/SQL i think the length can be up to 32767 bytes. Give it a try, Here is the link to the function:

Stragg Distinct

Gianni Ceresa · Answer

As now you are the 2 versions depending of where the error comes from it's up to you to do the next step.

986359 wrote:... then report errors out.

Post the error and/or a screenshot of the error.

Thomas Dodds · Answer

LISTAGG function in Oracle has the restriction of 4000 characters too ...

Given you are blowing through a DB limit, might be the time to redesign a physical structure so you aren't trying to function-ship something like this back and forth over the network.  Seems very inefficient, given you could process the data locally to the database and store it in a way that is easily retrieved and used.