Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Group by rollup problem

    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.
    Last edited by WindInFace; 04-27-10 at 12:48.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    Thank you tonkump!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •