Base Query won't run in split

[JoeCastor]

A query that runs in an embedded database won’t run in the split database (actually it ran a couple of times, but won’t now??) The error is “The data content could not be loaded. User lacks privilege or object not found: gap_ends_at”.
I compared the SQL from both the embedded and split queries and they are exactly the same as best as I can see.
Any help will be appreciated. The SQL is:

SELECT ( "t1"."MemberID" + 1 ) AS "gap_starts_at", ( SELECT MIN( "t3"."MemberID" ) - 1 FROM "MemberMaster" "t3" WHERE "t3"."MemberID" > "t1"."MemberID" ) AS "gap_ends_at" FROM "MemberMaster" "t1" WHERE NOT EXISTS ( SELECT "t2"."MemberID" FROM "MemberMaster" "t2" WHERE "t2"."MemberID" = "t1"."MemberID" + 1 ) HAVING "gap_ends_at" IS NOT NULL

[Ratslinger]

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")

[JoeCastor]

Perfect. You’re terrific. Looks I need to learn a lot of new functions.
I had a few other queries that didn’t run, but those three were because of the function TODAY which is not recognized in 2.x . CURRENT_DATE worked.
Thanks again,
Joe.

[Ratslinger]

That is a bit of a problem with SQL from my side. It is extremely important to know what is being used as to know how to help. There are also differences between products like HSQL to MySQL to PostgreSQL, etc. On your side, the extra functionality (as you have already witnessed) can give you better results with less coding. Be sure to download the manual for your version (there are newer versions - 2.4 I believe). Don’t have location off-hand but know PDF is out there. Glad you’re satisfied.

[JoeCastor]

As a FYI, I solved the extra line in the original query (first one above) by deleting the IS NOT NULL as you instructed and adding a BETWEEN 1 AND 246 (1 to 246 is my current number range) at the end. Worked. Now I have two methods so the users can choose. I actually liked your first solution better than what I was trying to get originally (second solution).
Thanks again.

[Ratslinger]

Thanks for the info. Would have fixed the original but thought you weren’t satisfied with that. Did find two problems with your fix - should start with 0 & need to change SQL when limit changes. Try this:

From original SQL replace HAVING "gap_ends_at" IS NOT NULL

with and "t1"."MemberID" BETWEEN 0 AND (SELECT max("MemberID") -1 FROM "MemberMaster").

[JoeCastor]

Thank you for this last change. This works because I had added a placeholder record to indicate the last/highest possible record (long story how the number limit developed). As the membership increases, it will bump that out correspondingly without intervention.