DB2 functions returning error SQL CODE 4743?

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

​x
 
ID     |Classes    |Priority----------------------------3       A51         13       B51         23       K5B         23       M5A         24       XN5         15       XN5         15       A51         29       BX1         19       BX2         29       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;

 
SELECT    ID,    LISTAGG(classes, ',') within GROUP (ORDER BY Priority) AS GROUPED_CLASSESFROM    StudentsGROUP 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

 
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.292) [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

 
ID    |Grouped_Classes     |-----------------------------3      A51, B51, K5B, M5A4      XN55      XN5, A519      BX1, BX2, AK3​

Answer

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

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

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

Advertisement

Answer