Re: Upper case function in index?

Nils asks: >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 In v 7.3 you can write SELECT UPPER(name) FROM customer WHERE name = UPPER("NAME") which will return all the uppercase values of NAME, but not the lowercase values (name) >And will it be able to use an index created like this: > >create index customer_upname on customer(myupname(name)); Functional Indexes are not part of the v7.3 engine. They are allowed in v9. >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? It's your responsibility to use these functions to store the data in an efficient format. (Or to upgrade to v9.) If you index a column of mixed-case data the optimizer will have to scan both the upper case and lower case regions of the index to satisfy the query (at least, it may have to scan the entire index, if not the entire table, converting values as it goes.) >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? Example 1 will work except for the UPPER function in the ORDER BY, which generates a syntax error. The case functions do not seem to work in every combination, such as an INSERT clause. They do work in SELECT, UPDATE, and WHERE clauses. As you learn more about these features, keep in mind the invitation in the introduction of TFM to contribute your thoughts on how to make it better. (Last page of the introduction.) Example 2 generates both a syntax error and a "order by must be in select" error. >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. I agree. These tests were run on 7.30.UC1N6. Note that the software will be downloadable on the Try&Buy page of the Informix web site at http://www.informix.com when it becomes generally available, and you can run these tests yourself. Alternatively, when TFM becomes available they will be on the TechInfo page for download and perusal by supported, registered users. These can be good ways to perform product research and testing in advance of actually buying it. _____________________________________________________________ Clem Akins (aka clem@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Motorcycling through shady redwood groves near SF