You could create and use an additional column to save the uppercase value,
filling it by triggers and indexing it for your needs or you could give a
look in IBCollation by David Schnepper on http://www.brookstonesystems.com/

Luiz.

----- Original Message -----
From: "Chuck Belanger" <phytotech@...>
To: "IB-Support Group" <ib-support@yahoogroups.com>
Sent: Saturday, September 07, 2002 6:00 PM
Subject: [ib-support] How to index on UPPER()? or some other solution


> 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?
>
> Something about adding UDFs? (never attempted to do this, btw).
>
> Here's the SQL if you think it would help:
> Its querying a self-referencing table. The param StrToCheck is all upper
> case. There are indexes on everything, except the UPPER(item_name).
>
> Thank you,
>
> Chuck Belanger
> Richmond, CA
>
> SQL.clear;
> SQL.Add('select v1.root_parent_id,');
> SQL.Add(' v1.parent_id,');
> SQL.Add(' v2.item_name as Root,');
> SQL.Add(' v3.item_name as Parent,');
> SQL.Add('v1.proprietary ');
> SQL.Add('from virtuallibrary v1');
> SQL.Add(' join virtuallibrary v2 on');
> SQL.Add('v1.root_parent_id = v2.item_id ');
> SQL.Add(' join virtuallibrary v3 on');
> SQL.Add('v1.parent_id = v3.item_id ');
> SQL.Add('where UPPER(v1.item_name)=:StrToCheck');
>
> KeyLinks.Clear;
> KeyLinks.Add('root_parent_id = item_id ');
> KeyLinks.Add('parent_id = item_id ');
>
> prepared := true;