SQL/DB2 concatenate rows into one field (DB2)

Question

Trying to concatenate values in a table in conjuction with a inner join, is this possible, sample code attached.

Code will work if i remove the CM.CMCSNM column

SELECT trim(CM.CMCSNM), CALLLOGP.CHSTKY, 
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',CALLLOGP.CHORD#))) as 
VARCHAR(1024)), 3) AS Batches
FROM LS3DTA100A.CALLLOGP CALLLOGP inner join LS3DTA100A.CM CM on 
CM.CMSTKY=CALLLOGP.CHSTKY 
WHERE (CALLLOGP.CHOPENBY<>'') 
AND (CALLLOGP.CHCLOSDT=0) 
AND (CALLLOGP.CHACKNDT=0) 
AND (CALLLOGP.CHUPDATE=0) 
AND (CALLLOGP.CHCOMMENTS Like '%FRAME OOS%') 
AND (CALLLOGP.CHSTKY Like '00%') 
GROUP BY CALLLOGP.CHSTKY

Error message is SQL Error [42803]: [SQL0122] Column CMCSNM or expression in SELECT list not valid.

Answer 1

SQL0122N  A SELECT statement with no GROUP BY clause contains a column
      name or expression and a column function in the SELECT clause, or
      a column name or expression is contained in the SELECT clause but
      not in the GROUP BY clause.

you need to include trim(CM.CMCSNM) as part of your GROUP BY. For example:

GROUP BY trim(CM.CMCSNM), CALLLOGP.CHSTKY