Subject How to index on UPPER()? or some other solution
Author Chuck Belanger

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.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.Add('root_parent_id = item_id ');
KeyLinks.Add('parent_id = item_id ');

prepared := true;