Question

6
Replies
944
Views
_DmitryK7052 Member since 2016 14 posts
Philips
Posted: August 19, 2016
Last activity: August 23, 2016
Closed
Solved

How to use LISTAGG function in Report Definition?

Hi all,

I've worte following user function:

Add it to my Report Definition:

Now that RD generate following SQL code:

SELECT LISTAGG("PC0".TEAMMEMBERNAME, ',') WITHIN GROUP (ORDER BY "PC0".TEAMMEMBERNAME) AS "pyTextValue(1)", "PC0".CASEID  FROM PRPC.MYORDERS_TEAMMEMBERS "PC0";

And it executes with error:

ORA-00937: not a single-group group function

Yes, this right, this function will not work without group by statement at the end of sql query:

SELECT LISTAGG("PC0".TEAMMEMBERNAME, ',') WITHIN GROUP (ORDER BY "PC0".TEAMMEMBERNAME) AS "pyTextValue(1)", "PC0".CASEID  FROM PRPC.MYORDERS_TEAMMEMBERS "PC0" GROUP BY CASEID;

Question: how to add this "GROUP BY CASEID" in Report Defenition?

Data Integration Reporting
Moderation Team has archived post
Share this page LinkedIn