ListAgg errors out in OBIEE 11g

986359

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

Gianni Ceresa

Hi,

You mean the OBIEE error of the varchar being bigger than 4000 ?

(Just to be sure it's not your DB returning a ORA-xxxxx error)

OBIEE has varchars up to 4000 and from an analytical point of view it's generally big enough.

Some historical workarounds I saw around was to split the longer column into many columns of 4000 each and then display them one next to each other so at least put everything on the screen.

Thomas Dodds

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.

Gianni Ceresa

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.

deeps

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