Very Computer

Does informix have any functionality to facilitate a case insensitive
order by clause (or a "to upper" function for the select clause)?  

Thanks,

Rob Donat.

it is not available in 7.2.  However, I have hear that in the 8.0
Server, the function will be available as a datablade function on a
string type. IMO the extra baggage of this server may be worth these
fantastic functionalities.

        -- Jake Salomon

Universal Server (lev 9.0) allows a UDR (user defined routine) to be used
within
the where clause.  Additional, an index can be defined to be the output of
such a
routine.  That is, it is possible to build an index of all uppercase
characters, and
to search on uppercase.  Also, it is possible to create an 'equality'
logic within
this UDR which causes prefixes such as Mac, MAC, Mc, etc. to be considered
equal.  Thus it is very easy to allow a search of McDonald to consider all
of the
various ways of spelling Mc to be considered the same.

For case insesitive searches, the systax would be:

Select * from name where upper(name) = "JONES";

For the index the syntax would be:
Create index namidx on name (upper(name));

Madison Pruet

:>
:> Universal Server (lev 9.0) allows a UDR (user defined routine) to be used
:> within
:> the where clause.  Additional, an index can be defined to be the output of
:> such a
:> routine.  That is, it is possible to build an index of all uppercase
:> characters, and
:> to search on uppercase.  Also, it is possible to create an 'equality'
:> logic within
:> this UDR which causes prefixes such as Mac, MAC, Mc, etc. to be considered
:> equal.  Thus it is very easy to allow a search of McDonald to consider all
:> of the
:> various ways of spelling Mc to be considered the same.
:>
:> For case insesitive searches, the systax would be:
:>
:> Select * from name where upper(name) = "JONES";
:>
:> For the index the syntax would be:
:> Create index namidx on name (upper(name));
:>
:> Madison Pruet

:It sounds as if you can basically write a small function to be called by
:a where clause.  Is that correct?

Not only in the where clause. As I understand it such a function can
be used basically anywhere you can use a column name. As in the
examples above in create index, and it will also work in your select
list.

:  Is it a stored procedure?
It's a user defined function implemented in a DataBlade and written in
C (or possibly C++ as well?).
Informix have also announced that such functions will be possible to
implement in Java.
Someone at Informix Norway have written a small DataBlade and says
it's simple to do.

NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org

A UDR (User Defined Routine) is any function which is written by the user
and can be invoked directly like a stored procedure (stored function in
universal server) or implicitly.  They can overide built in functions
which allows for support standard symbols to have different meanings,
depending on type definition.  For instance, the meaning of "=" can be
overridden so that "Mc", "MCC" and "Mac" are considere the same.  Also,
they provide the basic support of UDT (User Defined Types) which provide
the ability to define data types outside of the SQL built-in types.

UDRs are fairly close to the "methods" as defined in SMALLTALK.  They can
consist of a Stored Function using SPL or a 'c' module.  Work is currently
being done to provide support for JAVA as well.  When the 'c' module is
invoked, the
object module is loaded by the engine and executes as though it were part
of the engine.  They can also provide direct access to non-database media.
 For instance, the actual 'update' of the data could actually send a
control sequence to
thermostat control devices while the 'select' could actually be retrieving
the current
readings from heat sensors. --- Kinda 'Buck Rodgers' but it gives an idea
of what's
coming.

Madison Pruet