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