Re: Upper case function in index?
On Wed, 11 Mar 1998 15:16:07 +0200, David & Adina Samson
<samsons@netmedia.net.il> wrote:
Is this then a synax that will work?
1.
select myupcase(name) as upname, name, otherdata
from customer
where name = myupcase("A Name")
order by upname
And will it be able to use an index created like this:
create index customer_upname on customer(myupname(name));
Assuming of course that the optimiser finds this the best index.
In the above case will it be able to use the same index both for
selection and ordering?
May be the syntax is a little different but the idea is the important
thing.
Of course you will have to be able to do like this as well for the
select:
2.
select name, otherdata
from customer
where name = myupcase("A Name")
order by myupcase(name)
or even:
3.
select otherdata
from customer
where name = myupcase("A Name")
order by myupcase(name)
Is this possible? Both 2 and 3 above or only 2? The difference from
select number 1 above is of course that the "column" used in the order
by isn't mentioned in the select as is normally a requirement. I think
it's per the ANSI standard that columns used in the order by has to be
in the select clause as well. However with the kind of functionality
we get with UDO this have to be relaxed. Otherwise we may have to
select a lot of data used only for ordering and not by the application
that wants the data. Such a requirement would of course be
unfortunate.
This relaxation of the requirements of order by may even be in 7.3 DS
as other engines seems to have this capability. Can anyone confirm
that?
These things are some minor parts of the reason why I claim that
everyone realy need UDO (although few know it yet) and if Informix
made it available to everyone they would have had a significant
advantage in the market that wouldn't be all that easy to take away
from them. May be they'll make the UDO their standard database at one
time.
>FYI, in Universal Server they've added something called a "functional
>index". This enables you to create an index on the results of a
>function. Therefore, you don't need to store it twice (once regular and
>once in CAPITALS).
>
> David
>
>David Samson, Senior Database Researcher dsamson@ndsisrael.com
>NDS Technologies Israel Ltd. +972 2 589-4529
>PO Box 23012 Fax: +972 2 589-4578
>Jerusalem, Israel Cellular: +972 51 287 481
>
>
>
>Xin Zhou wrote:
>
>> Hi, there,
>>
>> I assume some of you must experience the same situation. I need
>> building
>> an index upon people's name. I would like sort them in the order of
>> either upper case or lower while name field allows mix-cased. I can
>> not
>> find a way in Informix to do it so I have to add a column of
>> single-cased name for sorting purpose only. It is kind of waste, isn't
>>
>> it? Can any of you out here shed a light on the issue?
>>
>> Thank you in advance.
>>
>> Sims
>> xhsims@erols.com
>
>
>
>
>--------------244A32E1C2A70D08F1632F26
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><HTML>
>FYI, in Universal Server they've added something called a "functional index".
>This enables you to create an index on the <U>results</U> of a function.
>Therefore, you don't need to store it twice (once regular and once in CAPITALS).
>
><P>
>David
>
><P>David Samson, Senior Database Researcher
>dsamson@ndsisrael.com
><BR>NDS Technologies Israel Ltd.
>+972 2 589-4529
><BR>PO Box 23012
>Fax: +972 2 589-4578
><BR>Jerusalem, Israel
>Cellular: +972 51 287 481
><BR>
><BR>
>
><P>Xin Zhou wrote:
><BLOCKQUOTE TYPE=CITE>Hi, there,
>
><P>I assume some of you must experience the same situation. I need building
><BR>an index upon people's name. I would like sort them in the order of
><BR>either upper case or lower while name field allows mix-cased. I can
>not
><BR>find a way in Informix to do it so I have to add a column of
><BR>single-cased name for sorting purpose only. It is kind of waste, isn't
><BR>it? Can any of you out here shed a light on the issue?
>
><P>Thank you in advance.
>
><P>Sims
><BR>xhsims@erols.com</BLOCKQUOTE>
>
><BR> </HTML>
>
>--------------244A32E1C2A70D08F1632F26--
>
>
>
Nils Myklebust
NM Data AS
Norway
E-mail: Nils.Myklebust@nmdata.com
FAQ at: Primary with ODBC info: http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html