Re: Upper case function in index?

Nils Myklebust (Nils.Myklebust@nmdata.com) wrote: : 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)); Actually this query will not rturn the answer you want. select myupcase(name) as upname, name, etc from customer where myupcase(name) = myupcase("A Name") order by upname; will be fine. : 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? Well, it will use the index for the lookup, and it will be able to sort the uppercase appropriately. Actually, the sort/indexing stuff will simply re-use the built-in operations over the varchar() stuff. OTOH, if the 'name' was a new data type (PersonName) and you had defined an interface that the engine could use, then your routines would drive the whole thing. : it's per the ANSI standard that columns used in the order by has to be : in the select clause as well. Actually, SQL lets you use the column alias (foo as bar) or the column numbers (order by 3,2,1;). : 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. Try this on for a thought experiment: SELECT name FROM customers where Birthday(DOB) = Birthday(TODAY); (Find me all customers who were born this day, this month.) (and index it . . . ) Well, the struggle at the moment is to explain to Gartner et al that the amount of money the DBMS vendor pays them is unrelated to the quality of that vendor's product.