Ask Your Question

Query works in design SQL but nowhere else

asked 2020-06-14 20:02:38 +0100

TrevJ gravatar image

In Forms in Base I am trying to filter a table on a subform with a filter table on the form.

My filter query on the subform is :-

SELECT * FROM "WordsTable" WHERE (UPPER("English") = UPPER(:EnglishFilter) OR :EnglishFilter IS NULL) AND (UPPER("Spanish") = UPPER(:SpanishFilter) OR :SpanishFilter IS NULL) AND ("Part of Speech" = :SpeechFilter OR :SpeechFilter IS NULL)

This works in SQL view in the query designer but if I go to design view I get as shown in the attachment QueryMess C:\fakepath\QueryMess.jpg

This gives the following SQL :-

SELECT * FROM "WordsTable" WHERE ( "Part of Speech" = :SpeechFilter AND UPPER ( "English" ) = UPPER ( :EnglishFilter ) AND UPPER ( "Spanish" ) = UPPER ( :SpanishFilter ) OR "Part of Speech" = :SpeechFilter AND UPPER ( "English" ) = UPPER ( :EnglishFilter ) AND :SpanishFilter IS NULL OR UPPER ( "English" ) = UPPER ( :EnglishFilter ) AND UPPER ( "Spanish" ) = UPPER ( :SpanishFilter ) AND :SpeechFilter IS NULL OR UPPER ( "English" ) = UPPER ( :EnglishFilter ) AND :SpanishFilter IS NULL AND :SpeechFilter IS NULL OR "Part of Speech" = :SpeechFilter AND UPPER ( "Spanish" ) = UPPER ( :SpanishFilter ) AND :EnglishFilter IS NULL OR "Part of Speech" = :SpeechFilter AND :SpanishFilter IS NULL AND :EnglishFilter IS NULL OR UPPER ( "Spanish" ) = UPPER ( :SpanishFilter ) AND :SpeechFilter IS NULL AND :EnglishFilter IS NULL OR :SpanishFilter IS NULL AND :SpeechFilter IS NULL AND :EnglishFilter IS NULL )

This also happens if I save the query in SQL view and then open it again.

If I run the query in Tools>SQL I get the following error :-

1: Assert failed: S0000 Direct execute with param count > 0java.lang.Exception at org.hsqldb.Trace.getStackTrace(Unknown Source) at org.hsqldb.Trace.doAssert(Unknown Source) at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source) at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source) at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source) in statement [SELECT * FROM "WordsTable" WHERE ( UPPER ( "English" ) = UPPER ( :EnglishFilter ) OR :EnglishFilter IS NULL ) AND ( UPPER ( "Spanish" ) = UPPER ( :SpanishFilter ) OR :SpanishFilter IS NULL ) AND ( "Part of Speech" = :SpeechFilter OR :SpeechFilter IS NULL )]

I am running Linux Mint 19.1 LibreOfficeVersion: with HSQLDB

I thought I had this working once but now I can't get it to go.

edit retag flag offensive close merge delete



For me saving the query in SQL view and not changing to design view, the SQL remains OK no matter what. It only goes bad when viewing in design mode. Even then, if not saved (and closing does not asked to be saved), the original entry remains. Report as a problem -> Bugzilla.

As for Tools->SQL, don't recall parameters working there.

Ratslinger gravatar imageRatslinger ( 2020-06-14 20:29:24 +0100 )edit

Sorry, I did not see the menu option Edit in SQL view. Just selected the edit option. Will report as problem. Thanks for the patience and prompt help.

TrevJ gravatar imageTrevJ ( 2020-06-14 23:25:13 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-16 23:26:57 +0100

TrevJ gravatar image

This was not a bug but a feature. The criterion are parsed Left to right with AND between each column going across and OR when going down to the next row. It will work as I was hoping for one column but not for more.

I have submitted an enhancement request.

edit flag offensive delete link more



As your question is written, what you experienced and I duplicated by switching between design & SQL modes, this is a BUG! What you actually reported is a feature request and not what your question stated.

Ratslinger gravatar imageRatslinger ( 2020-06-17 01:11:31 +0100 )edit

Yes, I realised after I wrote the comment. I will submit another bug report as the other one is marked as complete. I will get this right in the end!

TrevJ gravatar imageTrevJ ( 2020-06-17 14:04:49 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-06-14 20:02:38 +0100

Seen: 42 times

Last updated: Jun 16 '20