Hi, Helen!

Thanks for the tip. I'm still very new to some of the IB concepts (coming from
FoxPro), such as triggers, so I really appreciate your kindness to include some
detail in your answer.

It works great. What was taking about 27 seconds to check for duplicate
additions (also all the locations trees) for a text file list of items to be
added to the database, now takes less than a second.

I tried real hard to make the collate option work as suggest by Luiz, but it
just wasn't working at all. The test sql didn't work. It was looking like the
dll that was supplied wasn't even loading (I could delete it when I shouldn't
have been able to).

Thanks again,

Chuck Belanger
Richmond, CA

Helen Borrie wrote:

> At 02:00 PM 07-09-02 -0700, you wrote:
> >Hello:
> >
> >I feel like I have a simple problem and hope someone can help:
> >
> >I'm trying to do fairly straight forward SQL statement in IB, but its
> >incredibly slow (in local mode, too), because I need an index on
> >UPPER(field_name), but in IB it seems that I can't do that. All I can do
> >is add a new field and keep this all upper case.
> >
> >There must be an easier way! Is there?
>
> No, this is how we all do it to get a case-insensitive index for searching
> and ordering. It's easier than you think. Just create a column the same
> size as the one on which you want to do the uppercase search. Index this
> column, not the one which is being proxied. Then add BI and BU triggers to
> main the proxy column, viz.
>
> create trigger bi_atable active before insert as
> begin
> if (new.origcolumn is not null) then
> new.proxycol = upper(new.origcolumn);
> end
>
> Your beforeupdate trigger would be similar.
>
> Then in your select statement, do your order by on the proxy column,
>
> select origcolumn, proxycol from atable
> order by proxycol
>
> Goes like lightning.
>
> I see you are using IBO. IBO even has some inbuilt tricks to utilise such
> proxy columns in its search and reordering capabilities, i.e. you just tell
> the dataset the name of the proxy column and the component takes care of
> the rest. Off-topic here but look at the CASE INSENSITIVE flag on the
> ColumnAttributes page of the dataset editor - the field alongside this
> flag, labelled 'Upper-case equivalent' is for identifying the proxy column.
>
> heLen
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/