Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

For a temp fix, remove HAVING "gap_ends_at" IS NOT NULL. It will produce an extra line at the end (pointing beyond end) but otherwise is correct.

This SQL was originally presented because of limitations in HSQLDB v1.8 embedded. Will look at better list (quite possibly as complete individual items) since you have now upgraded to a newer DB.

For a temp fix, remove HAVING "gap_ends_at" IS NOT NULL. It will produce an extra line at the end (pointing beyond end) but otherwise is correct.

This SQL was originally presented because of limitations in HSQLDB v1.8 embedded. Will look at better list (quite possibly as complete individual items) since you have now upgraded to a newer DB.

Edit:

This should get you the listing you actually wanted from the start:

SELECT  MissingID
FROM   unnest(sequence_array(0, (SELECT max("MemberID") FROM "MemberMaster"), 1)) as i(MissingID)
WHERE MissingID NOT IN (SELECT "MemberID" as MissingID FROM "MemberMaster")