CODEWITHSUNDEEP
javasqloracle-databasehsqldbhierarchical-query
Harshit Agrawal
Harshit Agrawal

Reputation: 913

Connect By in HSQL DB

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.

Upvotes: 2

Views: 1508

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

TommCatt
TommCatt

Reputation: 5636

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.

Upvotes: 0

Related Questions