The powerfilter and the LIKE function for SQL

[RPG]

The powerfilter made clear by Villeroy is working good for listboxes. On some places Villeroy made clear that the parameters of the slave fields can be used like variables. There I did not studied it enough it was not clear to me that the idea can also be used in combination with like. When I understand how I can use the names in the slave fields as variables in combination to make a new SQL command in that data-form it was working nice. As far I knew it is not possible to work with queries or table in combination with LIKE. This is working:

Code: Select all

SELECT "Naam", "TitelBoek", "NAAMID", "BOEKID" FROM "Toon_Alles2"  
where upper("Naam") LIKE upper(:SchrijverNaam) AND upper("TitelBoek") LIKE upper(:TitelBoek)

I have the idea it works futher in the same way as the listboxes in the powerfilter but I do not use a lot of listboxes. But now I can use this I'm happy.

In https://www.openoffice.org/documentatio ... rm|outline it is describes how to use. But there is no reason to read the complete article there all the macro code is now in Apache since version 1.2.
I think only the part where the link point to it is important.

[Villeroy]

As far as I know (did a quick test right now), the LIKE operator does not match any substrings when no patterns are used. upper("Naam") LIKE upper(:SchrijverNaam) gives the same result as upper("Naam") = upper(:SchrijverNaam)

If you want to look up substrings, use SQL % and _ which are the jokers corresponding to * and ? in shell patterns.
upper("Naam") LIKE '%'|| upper(:SchrijverNaam) ||'%' matches the value of parameter :SchrijverNaam anywhere within "NAAM".
upper("Naam") LIKE '%'|| upper(:SchrijverNaam) matches the value of parameter :SchrijverNaam at the end of "NAAM".
upper("Naam") LIKE upper(:SchrijverNaam) ||'%' matches the value of parameter :SchrijverNaam at the beginning of "NAAM".
If "Naam" is declared as VARCHAR_IGNORECASE, you can omit the upper() function.

12 years old but still valid with HSQL 1 and 2: https://forum.openoffice.org/en/forum/d ... hp?id=6442

Power filtering works with all simple (non-binary) types such as booleans, dates, times, integers (with list boxes), all kinds of decimals and text types (including memos). With listboxes you need to be aware that the displayed listbox string is covering the actual value which is an integer in most practical use cases. The listbox string is a "lookup value".

[RPG]

Villeroy thanks for your comments maybe it is also more clear for other people and my explanation are not so clear most of the time. I use it always in a textbox and I can now quit a lot of macro's so live is more easy. In the textbox I do type the % and text I need.