Hi Martin,

OK, it's a lot clearer now, also thanks to the Dutch text. I'll just
reply in English so everybody can understand and maybe comment on it.

You have a generic edit box that can be used to type a search string.
There is also a combobox whose value determines which column the
search is applied to.

Furthermore, this is an incremental search: with every typed key, the
search (i.e. the WHERE clause of your query) is updated.

You want to allow your users to type a search string like "yes" or
"no" although in the column MULTIPACK the possible values are Y and N.

(Maybe you want this because these field values are SHOWN as Yes and
No, or as Ja and Nee for that matter?)

Now, as soon as the user types the first Y or N, things should work
fine just like with the other columns. But when the user adds the 'e'
from Yes, the generic method breaks because there is no 'Ye' or 'YE'
in column MULTIPACK.

Does this describe your problem correctly?

If so, the solution that you want to build is to convert

'Y', 'ye', 'Yes', 'YES' etc. on the user side

to

'Y' on the database side.


Same for the No-stuff of course.

You can implement this solution in the application:

- intercept the keystrokes;
- check if we are searching in column MULTIPACK;
- if so, check in your code if the uppercased keystrokes form a
substring of 'YES' or 'NO';
- if they do, just feed 'Y' or 'N' to whatever routine constructs
the WHERE claus in the query;
- if they don't, feed that routine an 'X' to get an empty result set.


You can also implement it in the database. For instance, you can
create an extra table MULTIPACKWORDS with two fields:

MULTIPACKLETTER varchar(1)
MULTIPACKWORD varchar(3)

You populate this table with two records:

Y YES
N NO

(or four, if you want to map 'y' and 'n' separately).


Then you change the query so it joins MULTIPACKWORDS too, and instead
of A.MULTIPACK it shows MULTIPACKWORDS.MULTIPACKWORD


Alternatively, you could add an extra column to A (don't remember full
name ;-)) that is Yes wherever Multipack is Y and No where Multipack
is N, and write a trigger to always update this column correcty.

This takes up more space, but saves you the extra table and the extra
join.


Or, you could make a selectable stored procedure that does most of the
work your query does now, except that it changes Y -> Yes and N -> No.

Then you display and search that procedure instead of your query. The
nice thing about this is that you can still use A.* and it will only
show the Yes/No (long) Multipack column; you can leave out the Y/N
column. With the solutions above you can only accomplish this if you
drop the * and specify all the columns by name.


BTW: I noticed you used: LIKE '%Y%'

If you build you search criterion like this:

% + user input + %

...and you want case-insensitivity, it's easier to just use:

CONTAINING + userinput

because this is case-insensitive by itself.



Nou, hopelijk heb je hier wat aan!

Groeten van Paul

On Sun, 9 Mar 2003, supertokkie wrote:

> The type of field multipack is Varchar(1), so only one character can
> I store there. I store there Y or N (so uppercase).
>
> In my program I have an edit component, where the user can insert the
> search text.
>
> I want to let the form work general. So I don't want to show 2 radio
> buttons with the text Yes and No when the user select the search area
> field (a combobox, which tell the system where it must search, in
> which column from the table): Multipack.
>
> So the user has selected the search area field: Multipack. Then he
> know that he can type Yes or No in the search field.
>
> Because I want to refresh the search result after every key input, I
> need a function to convert the real data stored in the table to the
> value that the user will be pressed in the edit search component.
>
> Then it is possible to check after each key press.
>
>
> Paul I think you are a dutch man, so I will also write it in Dutch
> because maybe it's more clear:
>
> Ik heb dus een formulier waarop een combobox staat, waar je aan kan
> geven in welke kolom je van de tabel wil gaan zoeken. Als de
> gebruiker Multipack selecteert, zal het systeem dus alleen in die
> kolom gaan zoeken.
>
> Natuurlijk zou je dan 2 radiobuttons met ja en nee kunnen weergeven.
> Op zich is dat mooier maar ik wil een algemene manier hebben. (Want
> ik kan ook kiezen: zoeken op alles en dan moet je een zoekstring
> invoeren en kan je dus ook niet 2 radio buttons weergeven).
>
> Maar goed in Multipack staat altijd Y of N. Maar ik wil dat de
> gebruiker gewoon ja of nee in kan tikken en dat het systeem bij de
> query eerst Y omzet naar ja en N omzet naar nee.
>
> Met uppercase zorg ik er voor dat allebei de kanten hoofdletters
> worden dus daar zit het probleem niet.
>
> Het gaat erom hoe ik in die select query een veld een andere waarde
> kan geven. Normaal gesproken doe je dat in SQL met CASE. Maar
> interbase ondersteunt dat niet.