I was writing test cases for query that uses connect by hierarchical clause. It seems that there is no support for this clause in HSQL Db.
Are there any alternatives for testing the query or writing a different query that does the same thing.
The query is simple
SELECT seq.nextval
FROM DUAL
CONNECT BY level <= ?
Thanks.
You don't need a recursive query for that.
To generate a sequence of numbers you can use sequence_array
select *
from unnest(sequence_array(1, ?, 1))
More details are in the manual:
http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#N14088
If you need that to advance a sequence a specific number of entries, you can use something like this:
select NEXT VALUE FOR seq
from unnest(sequence_array(1, 20, 1));
If you need that to set the sequence to a new value, this is much easier in HSQLDB:
ALTER SEQUENCE seq restart with 42;
If you are looking for a recursive query, then HSQLDB supports the ANSI SQL standard for that: recursive common table expressions, which are documented in the manual:
http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_with_clause
According to this 2-year-old ticket, only Oracle and a database called CUBRID have CONNECT BY capability. If you really want it, maybe you could vote on the ticket. However, as far as I have been able to tell, there are only two people working on the project, so don't hold your breath.
