[Kexi] LIKE syntax

[John Jason Jordan]

On Mon, 25 Feb 2008 09:47:30 +0100
Jarosław Staniek <js at iidea.pl> dijo:

> John Jason Jordan said the following, On 2008-02-22 17:59:
> > I have a field I use for selecting records in a database of test
> > questions by placing unique strings in the field. For example, I might
> > enter "FE1a-001" which means that it will be merged into Final Exam 1a
> > and will be question #1. This works well, but sometimes I need to merge
> > the same question into other documents. For example, if I want the same
> > question to appear in Final Exam 3c as question #30 I need to enter
> > "FE3c-030" in this field as well. 
> > 
> > I can add both values in the field, but then the select queries do not
> > pick up the record at all. I need the LIKE operator to function as long
> > as the string occurs *anywhere* in the field, not that the field be
> > *the same as* the string. I can't find any documentation that addresses
> > how to do this.
> 
> Pelase read Answer 1.9 at
> http://kexi-project.org/wiki/wikiview/index.php?KexiFAQ#1._General_Features
> 
> A side note: perhaps you could split your composed field to two separate 
> fields? Moreover, I can see here the many-to-many relationship:
> Questions <-> Exams, so splitting the data to separate tables could help as 
> well (that leads to using of drop-down lists in your table or form views).

It turns out that what I needed to do can almost be done with
wildcards. That is, a WHERE field LIKE '%FE1a-???%' will pick up all
records where FE1a- exists anywhere in the field. The problem is that I
also need the query to sort the data on the ???, which is the number
that the question will be in the exam. I couldn't get it to sort on the
expression, only on the entire contents of the field.

In struggling with this, and other current limitations (like not being
able to add a field without deleting all data) I decided that I would
be better off to switch to OOo Base. Moving the data to OOo Base was
not trivial - I got Base to connect to the Kexi database, but it was
unable to copy tables to a different Base database. That is, it could
perform functions on the Kexi database, including queries, but
everything it did had to stay in the Kexi database. Finally I had to
export tables from Kexi as CSV import those into a new Base database.
And even that was troublesome. I finally succeeded, however.

Now that I am using Base I can say that its query abilities (it uses
HSQL) are more robust than SQLite3, but its GUI for queries is not a
great deal more developed than Kexi's. On the other hand, I managed to
get the queries I needed selecting and sorting as I needed them. The
advantage of Base at this point over Kexi is that all features of HSQL
are available, albeit sometimes you have to write the statement
manually. In Kexi not all features of SQLite3 are yet available, and I
have work that I must deliver to a customer now. 

Having said that, Base is slower than the second coming. I miss the
speed of Kexi. Perhaps I'll move back to it someday when Kexi is
complete and Base can connect to it without having to mess with
ODBCConfig.

Thanks for the help and suggestions.