SQL query with IS LIKE clause - and user entry

[Got_LibreOffice]

Hello,
I'm new to OO_Base so I may be barking up the wrong tree.
But I would like to create a form which has a search field in it.
I would type in a search string and it would initiate a search based on a pre-made query

That query would have one WHERE clause which will be an IS LIKE clause - the string of which the user must enter at run time.

So for example - I would be searching a column in a table where some text within that table IS LIKE whatever the user enters at run-time.

I think I'm headed in the correct path in accomplishing this.
If so - then what I would be asking here is the Query Syntax for the IS LIKE clause requiring user entry at run-time

Sincere thanks in advance!

[robleyd]

Did you look at [Solved] Way to pass parameters into queries?

You don't mention which database you are using - Base is not a database but an interface to a number of database applications - but I don't know of any variant of SQL that has IS LIKE; there is LIKE however.

[Got_LibreOffice]

Sorry
That was my mistake
The DB is one created within OO_Base
So LIKE would be correct
Thanks

[F3K Total]

Hello,
two samples:
1. with parameter

Code: Select all

... WHERE ( LOWER ( "Name" ) LIKE LOWER ( :qName || '%' ) OR :qNameIS NULL )

2. using an extra filtertable

Code: Select all

... WHERE ( LOWER ( "Name" ) LIKE LOWER ( (SELECT "F_Name" FROM "Filter") || '%' ) OR (SELECT "F_Name" FROM "Filter") NULL )

Info:

Code: Select all

|| '%'

means, you have only to type the beginning of a word to match all words that begin like what you typed in.

[Got_LibreOffice]

I'm having trouble getting the system to take the query with the input parameter function included.

The field being queried contains sentences.
I am essentially looking for any sentence which includes a search string anywhere within the sentence
So if I am looking for sentences which contain the word "nothing" the following query works:

SELECT "Person", "Category", "Quote" FROM "tblQuotes" WHERE "Quote" LIKE '*nothing*'

But when I try to change the fixed string "nothing" to an input parameter - the system indicates the query is "to complex"

SELECT "Person", "Category", "Quote" FROM "tblQuotes" WHERE "Quote" LIKE '%' || :SearchString || '%'

[F3K Total]

i wonder, just tested your query, worked for me, using HSQLDB and FIREBIRD also.

[Got_LibreOffice]

i wonder, just tested your query, worked for me, using HSQLDB and FIREBIRD also.

Thanks!
I wonder if there is a difference in required syntax between OO_Base versions?

[Got_LibreOffice]

i wonder, just tested your query, worked for me, using HSQLDB and FIREBIRD also.

Thanks!
I wonder if there is a difference in required syntax between OO_Base versions?

I just discovered there is a difference between Open Office and LibreOffice.
I am not using Open Office - I have LibreOffice
Even so - when I read the LibreOffice documentation of adding wildcards to parameters it shows the system I've tried which the system does not like.

[UnklDonald418]

Instead of a parameter you could use a Filter Table.

Demo40A_FilterTable.odb

(24.66 KiB) Downloaded 202 times

[Villeroy]

Combine parameter query and filter table if the filtered row set needs to be editable.

[Mountaineer]

...
The DB is one created within OO_Base
...

Maybe you created a "datasource" not a database.
A source can also be a dbase-file (like the default bilbliography), a spreadsheet or even csv-Files. These are limited, and give often "to complex" as error.

The there a 2 types of databases "included", the default HSQLDB and Firebird. You find YOUR type in the lower status-bar of the database-window.

[Mountaineer]

...
I just discovered there is a difference between Open Office and LibreOffice.
I am not using Open Office - I have LibreOffice
Even so - when I read the LibreOffice documentation of adding wildcards to parameters it shows the system I've tried which the system does not like.

Main point: SQL does use % as a wildcard instead of the * of the file-systems.

Difference between OO and LO is quite neglectable concerning Base. LO has integrated the report-builde extension and delivers also Firebase.