Question

6
Replies
1008
Views
Dmitry Kolesnikov (_DmitryK7052)
Philips

Philips
NL
_DmitryK7052 Member since 2016 14 posts
Philips
Posted: August 19, 2016
Last activity: August 23, 2016
Posted: 19 Aug 2016 8:51 EDT
Last activity: 23 Aug 2016 4:52 EDT
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, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.