Can hsldb support rollup or something like?

Help
Peng Wang
2004-11-01
2014-11-11
  • Peng Wang
    Peng Wang
    2004-11-01

    Hi all,
    I want to make some statistic computing, but I cannot find any related topic in documentation or maillist. e.g. how can I implement the following statement(oracle 8i) in hsqldb?
    select to_char(return_date,'MONTH'), name, sum(fee)
    group by rollup(to_char(return_date,'MONTH'),name)

    Many Thanks

    Peng

     
    • Fred Toussi
      Fred Toussi
      2004-11-02

      HSQLDB doesn't support ROLLUP.

      You may be able to write a UNION which has the different rows and use another select with a GROUP BY clause around the UNION to order the results.

       
  • Dmitry Katsubo
    Dmitry Katsubo
    2013-04-16

    Would be nice to have rollup/cube options supported in HSQL. Any plans to add them?

     
  • Fred Toussi
    Fred Toussi
    2013-04-16

    There are no current plans. Perhaps next year or so.

     
  • Dmitry Katsubo
    Dmitry Katsubo
    2014-03-20

    Really wanted feature. I vote for it!

     
  • Fred Toussi
    Fred Toussi
    2014-03-20

    You can already do this

     
  • Fred Toussi
    Fred Toussi
    2014-03-20

    Example:

      CREATE TABLE JOBS (CLIENTID INT, JOBTYPE INT, FEE DECIMAL(10,2))
    
      INSERT INTO JOBS VALUES 11, 2, 12.40
      INSERT INTO JOBS VALUES 11, 2, 11.10
      INSERT INTO JOBS VALUES 11, 3, 2.10
      INSERT INTO JOBS VALUES 13, 3, 2.10
      INSERT INTO JOBS VALUES 13, 3, 1.10
    
      WITH JOBSTATS(CLIENTID, JOBTYPE, FEE) AS (SELECT CLIENTID, JOBTYPE, SUM(FEE) 
           FROM JOBS GROUP BY CLIENTID, JOBTYPE)
      SELECT CLIENTID, CAST(JOBTYPE AS VARCHAR(10)) AS JOBTYPE, FEE FROM JOBSTATS
         UNION
      SELECT CLIENTID, '', SUM(FEE) FROM JOBSTATS GROUP BY CLIENTID ORDER BY CLIENTID, JOBTYPE DESC
    
     CLIENTID JOBTYPE FEE   
     -------- ------- ----- 
     11       3       2.10  
     11       2       23.50 
     11               25.60 
     13       3       3.20  
     13               3.20
    
     
  • Dmitry Katsubo
    Dmitry Katsubo
    2014-11-11

    Fred, thanks for hint but I am afraid that your solution is too cumbersome. Imagine I have SQL with "group by ... with rollup" statement, which works fine in production. Now I want to cover by DAO with Unit tests. In order for them to work over HSQL, I will have to inject another SQL into DAO (which will be a decomposition or rollup like you have shown), thus SQL to be used for production cannot be tested.

    If that SQL decomposition is so easy, why cannot HSQL do it automatically internally? I won't say for all, but for me personally this functionality is more important than triggers and stored procedures. Pity that community cannot vote for features...

     
  • Fred Toussi
    Fred Toussi
    2014-11-11

    Any major functionality like this is very time-consuming to implement. Commercial users have not so far shown any interest in funding the development.