CODEWITHSUNDEEP
sqldb2
Shay Young
Shay Young

Reputation: 207

DB2 - Aggregate row data into comma list

i am reading a db2 table where the data is stored as

user    group  
-----   ------
user1   groupa  
user1   groupb  
user1   groupc  
user2   groupc  
user3   groupc

i want a single row returned for each user.

user1 groupa,groupb,groupc

i have been told by other forms the correct way to do this is below

SELECT VRAVW900_USER, SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',VRAVW900_Group))) as VARCHAR(1024)), 3) AS Groups
FROM VRADB2.VRAVW900 
GROUP BY VRAVW900_USER

This does not work for me and i get the error:

DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=VARCHAR;CLOB CHAR CHARACTER BLOB BINARY DBCLOB, DRIVER=3.64.133
    at com.ibm.db2.jcc.am.bd.a(bd.java:679)
    at com.ibm.db2.jcc.am.bd.a(bd.java:60)
    at com.ibm.db2.jcc.am.bd.a(bd.java:127)
    at com.ibm.db2.jcc.am.io.c(io.java:2706)
    at com.ibm.db2.jcc.am.io.d(io.java:2694)
    at com.ibm.db2.jcc.am.io.a(io.java:2143)
    at com.ibm.db2.jcc.am.io.a(io.java:2119)
    at com.ibm.db2.jcc.t4.ab.h(ab.java:136)
    at com.ibm.db2.jcc.t4.ab.b(ab.java:41)
    at com.ibm.db2.jcc.t4.p.a(p.java:32)
    at com.ibm.db2.jcc.t4.qb.i(qb.java:135)
    at com.ibm.db2.jcc.am.io.gb(io.java:2112)
    at com.ibm.db2.jcc.am.io.a(io.java:3191)
    at com.ibm.db2.jcc.am.io.a(io.java:686)
    at com.ibm.db2.jcc.am.io.executeQuery(io.java:669)
    at com.service_now.monitor.jdbc.JDBCRowSet.query(JDBCRowSet.java:64)
    at com.service_now.mid.probe.JDBCProbe.doSelect(JDBCProbe.java:312)
    at com.service_now.mid.probe.JDBCProbe.doQuery(JDBCProbe.java:198)
    at com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:123)
    at com.service_now.mid.probe.AProbe.process(AProbe.java:84)
    at com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:125)
    at com.service_now.mid.queue_worker.AWorkerThread.run(AWorkerThread.java:20)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

i am running this from ServiceNow, DB2 Version 11

Upvotes: 0

Views: 683

Answers (1)

Tanveer Singh Bhatia
Tanveer Singh Bhatia

Reputation: 426

Datatype of the VRAVW900_Group is not valid that's why the error -199.

However cast VRAVW900_Group as VARCHAR and use LISTAGG function for aggregating row data.

SELECT VRAVW900_USER,
LISTAGG(Cast(VRAVW900_Group as VARCHAR), ', ') AS Groups
FROM VRADB2.VRAVW900 
GROUP BY VRAVW900_USER

Check this link for usage of LISTAGG function.

Upvotes: 1

Related Questions