Group by rollup problem

WindInFace · #1 (**permalink**) 04-27-10, 11:39

Hi,

We're using DB2 Z/OS version 8.

When I try using GROUP BY ROLLUP this code returns the error
SQLCODE = -440, ERROR: NO ROLLUP BY THE NAME HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

Code:

WITH my_table AS (
	 SELECT 'A' MY_GROUP FROM SYSIBM.SYSDUMMY1
	 UNION ALL
	 SELECT 'B' MY_GROUP FROM SYSIBM.SYSDUMMY1
	 UNION ALL
	 SELECT 'B' MY_GROUP FROM SYSIBM.SYSDUMMY1
	 UNION ALL
	 SELECT 'C' MY_GROUP FROM SYSIBM.SYSDUMMY1
	 UNION ALL
	 SELECT 'C' MY_GROUP FROM SYSIBM.SYSDUMMY1
	 UNION ALL
	 SELECT 'C' MY_GROUP FROM SYSIBM.SYSDUMMY1
)
SELECT COUNT(MY_GROUP) COUNT, MY_GROUP
FROM my_table
GROUP BY  ROLLUP (MY_GROUP)

However removing ROLLUP and using GROUP BY (MY_GROUP) works fine, but of course I don't get the total.

tonkuma · #2 (**permalink**) 04-27-10, 13:42

Looking into manuals("DB2 Universal Database for z/OS SQL Reference Version 8" and "DB2 Version 9.1 for z/OS SQL Reference"),
DB2 for z/OS don't support GROUP BY grouping-sets nor GROUP BY super-groups(include ROLLUP) even on Version 9.

WindInFace · #3 (**permalink**) 04-27-10, 13:49

Thank you tonkump!

I'll have create my own total row with a union.

Internet Services

Web Development

Digital Marketing

Consumer Tech