Search code examples
hsqldb

Cannot read SEQUENCE value with WHERE clause in HSQLDB?

I was trying to get value based on the sequence number using WHERE clause,

However, it is returning Blank result.

I have tried both

select report_time from DATA
where idx = 1;

and

select report_time from DATA
where idx = '1';

Has anyone encounter this issue before in HSQLDB?

create sequence SEQID2 start with 1 increment by 1;

create view DATA as 
select next value for SEQID2 AS idx, report_time
FROM abc;

select report_time from DATA
where idx = 1;
Solution

  • Each time you execute SELECT * FROM DATA the query returns larger values for the idx column. The rows of the VIEW are not stable, therefore your query has no practical usage.