So I can get the effect today?

Something like:

Create table names (surname varchar(100) not null, surname_collate computed
by translate(surname));

create index on names (surname_collate);

select surname from names order by surname_collate;

Great! I must admit that I had assumed I couldn't index a computed field.
I should have checked. Telephone book ordering, here I come. I asssume a
CSTRING is collated using a pure binary comparison.

Expression indexing would allow the artificial field name to be removed from
the table. That would be nice, but functionally makes no difference.

Are there other collation requirements which aren't met by this? I believe
this could handle just about all the requirements that have been stated
through a UDF.

You don't happen to have operator overloading #ifdef'd out as well, do you?

Regards,

Jan.


-----Original Message-----
From: David Schnepper <dschnepper@...>
To: 'IB-Architect@onelist.com' <IB-Architect@onelist.com>
Date: Thursday, 30 March 2000 8:31
Subject: RE: [IB-Architect] Syntax for case insensitive sort

>From: David Schnepper <dschnepper@...>
>
>
>Deej implemented Expression Indices several years ago -- it's still in the
>codebase with an #ifdef (#ifdef EXPRESSION_INDICES, I think). It worked,
>but
>we never got test resources to develop a test suite for it.
>
>Incidently, one way to get what you want, right now.
>Create calculated column with the expression you want.
>Create an index on that column.
>For the optimizer to use the index, you must use the calculated column
>in the where clause eg: WHERE the_sum = 10 instead of WHERE (a+b) = 10
>when the_sum is defined as (a+b)
>
>Dave
>
>-----Original Message-----
>From: Jan Mikkelsen [mailto:janm@...]
>Sent: Wednesday, March 29, 2000 2:24 PM
>To: IB-Architect@onelist.com
>Subject: Re: [IB-Architect] Syntax for case insensitive sort
>
>
>From: "Jan Mikkelsen" <janm@...>
>
>
>The database has always had the ability to collate on the output of a UDF,
>so "order by ucase(field)" is a valid way to get case insensitive

collation.

>The issue is efficiency. Shock, horror! :-)
>
>I'd like to be able to define an index on the output of a UDF. Like:
>"Create [unique] index on tablename (translate(fieldname))".
>
>That way I can easily define my own collation order without needing involve
>the engine. I can index and collate in the order "McArthur", "MacDonald",
>"McMurtrie", "Salamander", "St. Etienne", "St. George", "Something", as

they

>are meant to be sorted for phone books. Who knows what the phonebook order
>is in Botswana? If I did, I could write the function and get the ordering.
>
>No new order by syntax is required, and the fundamental behaviour of
>operators doesn't need to change. Now, I'd like to be able to create new
>datatypes and redefine operator behaviour, but that is a whole separate
>issue.
>
>There are other advantages:
>
>create table locations (longitude real, latitude real, ...);
>create index on locations (distance_from_depot(longitude, latitude));
>select blah from locations where distance_from_depot(longitude, latitude) <
>whatever;
>
>(Maybe not a great example, but you get the idea.)
>
>Clearly more intelligence is required in the optimiser. It must look for
>indexes on expressions, not just columns in base tables.
>
>This is the approach used in Illustra/Postgres influenced systems like
>Informix and Tandem NonStop-SQL.
>
>The other option might be to allow user defined collations, and use the
>"collate" clause. A standard UDF interface where the field value is passed
>in, and a version that sorts correctly using a binary comparison is
>returned. Then you need a way to define indexes using a given collation

...

>
>Regards,
>
>Jan.
>
>
>
>------------------------------------------------------------------------
>@Backup- Protect and Access your data any time, any where on the net.
>Try @Backup FREE and recieve 300 points from mypoints.com Install now:
>http://click.egroups.com/1/2345/3/_/_/_/954368689/
>------------------------------------------------------------------------
>
>To unsubscribe from this group, send an email to:
>IB-Architect-unsubscribe@onelist.com
>
>
>
>------------------------------------------------------------------------
>Good friends, school spirit, hair-dos you'd like to forget.
>Classmates.com has them all. And with 4.4 million alumni already
>registered, there's a good chance you'll find your friends here:
>http://click.egroups.com/1/2622/3/_/_/_/954369057/
>------------------------------------------------------------------------
>
>To unsubscribe from this group, send an email to:
>IB-Architect-unsubscribe@onelist.com
>
>
>
>