Subject RE: [IBO] TIB_LookupCombo case insensitive inc search
Author Helen Borrie
At 04:06 PM 30-11-00 +0800, you wrote:
>Thanks for your reply.
>I changed the ColumnAttributes as you suggested. It may have altered the
>behavior somewhat but has not fixed the problem.
>If I enter 'corp ' or 'corp x' it displays 'corp x' in the edit box but the
>list does not scroll to display 'corp x' and it displays a message 'corp x
>was not found' as soon as focus moves away from the edit. Once again, if you
>scroll to the bottom of the list and click on 'corp x' then no error occurs.
>Also, 'corp x' is displayed at the end of the dropdown combo list box. I
>would expect to see 'corp x' listed immediately after 'Corporate
>BTW - This is your sample project I am using here. All I am doing is
>inserting a department that starts with a lower case letter. Please give it
>a try, I am sure you will see the problem immediately.
>Brian, As Jason said below:
> >
> >
> > You are not using the properties correctly. Don't put mixed
> > case values in a
> > column that is indicated to be the NOCASE equivalent column.

The way the NOCASE feature works is this:

For the MIXED CASE column on which you want the NOCASE feature to work, you
can define (in your database) a proxy column, populated by trigger on the
main column, which stores UPPER(TheMixedCasedColumnValue). You would place
a non-unique index on this column.

When you come to set up properties for the main (mixed case) column, you
check NOCASE and enter the column name of the column containing the
trigger-populated values. The match sought is

where ProxyColumn = UPPER(SearchValue)

As Jason said:

> >
> > By leaving it out IBO will use the UPPER( ) where
> > appropriate. Just keep in
> > mind that you lose the benefit of having an index so for
> > larger datasets
> > this is undesirable.

Meaning that, if the search is forced to be done on the mixed-case column
itself, the query to match the NOCASE criterion will be

WHERE UPPER(SearchedColumn) = UPPER(SearchValue).

That is, UPPERing mixed case SearchedColumn creates an expression, thus
denying you the benefit of any index the column has.

In the case of the sample, you are "double-blinding" it, by adding a mixed
case value to the proxy (otherwise all uppercase) set. In the first place,
it appears last in the dataset because everything preceding it is in upper
case and hence ahead of it in the collation sequence; and second, because
it does not match what is being searched for, which is UPPER(SearchValue).

All for Open and Open for All
InterBase Developer Initiative ยท