DB2 functions returning error SQL CODE 4743?

Question

I've been trying to solve this issue now for a while. I have a table called Students like:

ID     |Classes    |Priority
----------------------------
3       A51         1
3       B51         2
3       K5B         2
3       M5A         2
4       XN5         1
5       XN5         1
5       A51         2
9       BX1         1
9       BX2         2
9       AK3         2

I am using DBVisualizer right now to execute my statements, but I am trying to play around with something called LISTAGG() as a DB2 function:

SELECT
    ID,
    LISTAGG(classes, ',') within GROUP (ORDER BY Priority) AS GROUPED_CLASSES
FROM
    Students
GROUP BY
    ID;

However, every time I try to run this, I get this error:

1) [Code: -4743, SQL State: 56038]  ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=4.22.29
2) [Code: -514, SQL State: 26501]  THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=4.22.29

And I have no idea how to fix it or why it is like this. I tried researching into this and someone suggested to do this: SET CURRENT APPLICATION COMPATIBILITY = 'V11R1' but there was no luck with this either, same error even though it ran.

I also tried to look at the version of my DB2 with SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1 and this returns DSN12015 (no idea what this means).

I'm desperately looking for an answer or some help, thank you very much. The goal is to have the result look something like:

ID    |Grouped_Classes     |
-----------------------------
3      A51, B51, K5B, M5A
4      XN5
5      XN5, A51
9      BX1, BX2, AK3

Answer 1

Try this:

/*
WITH STUDENTS (ID, Classes, Priority) AS 
(
          SELECT 3, 'A51', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'M5A', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 4, 'XN5', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 5, 'XN5', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'B51', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'K5B', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 5, 'A51', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'BX1', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'BX2', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'AK3', 2 FROM SYSIBM.SYSDUMMY1
)
*/
SELECT 
  ID
, SUBSTR (XMLSERIALIZE (XMLAGG (XMLTEXT (',' || CLASSES) ORDER BY PRIORITY) AS CLOB (100)), 2)
  AS GROUPED_CLASSES
FROM STUDENTS
GROUP BY ID

ID	GROUPED_CLASSES
3	A51,K5B,M5A,B51
4	XN5
5	XN5,A51
9	BX1,BX2,AK3