Ask Your Question

Base Query won't run in split [closed]

asked 2017-08-31 20:27:21 +0100

Joe Castor gravatar image

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
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-09-01 05:05:12.199851

1 Answer

Sort by » oldest newest most voted

answered 2017-08-31 21:18:35 +0100

Ratslinger gravatar image

updated 2017-08-31 23:35:33 +0100

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.


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

FROM   unnest(sequence_array(0, (SELECT max("MemberID") FROM "MemberMaster"), 1)) as i(MissingID)
WHERE MissingID NOT IN (SELECT "MemberID" as MissingID FROM "MemberMaster")
edit flag offensive delete link more


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.

Joe Castor gravatar imageJoe Castor ( 2017-09-01 05:02:28 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-01 05:36:56 +0100 )edit

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.

Joe Castor gravatar imageJoe Castor ( 2017-09-01 15:31:29 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-09-01 15:47:30 +0100 )edit

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.

Joe Castor gravatar imageJoe Castor ( 2017-09-03 15:44:34 +0100 )edit

Question Tools

1 follower


Asked: 2017-08-31 20:27:21 +0100

Seen: 28 times

Last updated: Aug 31 '17