Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in... -posted to the "PS Query & SQL" group

PS Query & SQL Post New Discussion Listagg with PSMSGCATDEFN.MESSAGE_TEXT Reply to Thread Aug 18, 2025 4:47 PM Vic Goldberg Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in particular).  I'm doing what I normally do with listagg, setup an appropriately sized expression, check the aggregate box in the expression, and pop in the listagg statement.  It looks like this: listagg (distinct J.MESSAGE_TEXT,';').  When I try running the query with the expression as a field, I get the following error. Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380) I checked, message_text is a char100 field, and listagg is defined as returning varchar2.  No CLOBS in sight.  There is a descrlong, which is a clob, in PSMSGCATDEFN, but I'm not referencing it in any way. Any answers? ------------------------------ Vic Goldberg University of Colorado System goldbergvictor@gmail.com ------------------------------   Reply to Community   Reply to Sender via Email   View Thread   Recommend   You are subscribed to "PS Query & SQL" as jedobr@umich.edu. To change your subscriptions, go to My Subscriptions. To remove yourself from this community discussion, you can unsubscribe at any time.

Thanks!

Vic

---

Vic Goldberg

Retiree, Temporary Employee

University of Colorado Boulder

Independent PeopleSoft FA Consultant

Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in... -posted to the "PS Query & SQL" group

PS Query & SQL Post New Discussion Listagg with PSMSGCATDEFN.MESSAGE_TEXT Reply to Thread Aug 18, 2025 4:47 PM Vic Goldberg Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in particular).  I'm doing what I normally do with listagg, setup an appropriately sized expression, check the aggregate box in the expression, and pop in the listagg statement.  It looks like this: listagg (distinct J.MESSAGE_TEXT,';').  When I try running the query with the expression as a field, I get the following error. Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380) I checked, message_text is a char100 field, and listagg is defined as returning varchar2.  No CLOBS in sight.  There is a descrlong, which is a clob, in PSMSGCATDEFN, but I'm not referencing it in any way. Any answers? ------------------------------ Vic Goldberg University of Colorado System goldbergvictor@gmail.com ------------------------------   Reply to Community   Reply to Sender via Email   View Thread   Recommend   You are subscribed to "PS Query & SQL" as jedobr@umich.edu. To change your subscriptions, go to My Subscriptions. To remove yourself from this community discussion, you can unsubscribe at any time.