RE: Lists of Names

This will not work. : javi@psi.ernet.in (javeed anwar t) writes: : By moving to INFORMIX (hope it is ESQL/C), I feel that U can : use the following method: : : 1. DECLARE name_cur SCROLL CURSOR FOR : SELECT name,<other flds> : FROM <tabname> : ORDER BY name. With "hundreds of thousands of records" this will result in one mighty cursor. : 2. After the user enters the first few characters of the name (into few_chars), : execute the following SELECT stmt. to find the position of that : record in the cursor. : SELECT count(*) : INTO $pos : FROM <tabname> : WHERE name < $few_chars : ORDER BY name. And this will take an inordinate amount of time (except for a few cases where $few_chars is very early in the index order). (And the "order by" should of course not be there.) : 3. FETCH ABSOLUTE $pos+1 name_cur : will give you the first record for the few characters entered. Only if nothing has changed in the meantime (noone else has inserted or deleted anything). : 4. From there, Use : FETCH NEXT name_cur : OR : FETCH PREVIOUS name_cur : to get the remaining records to be displayed on the scree or to allow : browsing. And again this will often take a very long time as the cursor is now filled with data. : Hope this helps U Probably not. : M. Livenspargar wrote: : : :I'm curious: what are the best ways to handle lists of names in an : :Informix environment such that a user can enter a name and get a : :reasonable list of names from which to choose. In my current : :environment, which uses ISAM files, we have a large number of names, : :many of which are the same as others and are not in fact the same : :person. : : : :Currently, we allow the user to enter as much of the name as they : :choose to enter, then provide an onscreen alphabetical list such that : :the (partial) name entered would appear in the middle of the names on : :the screen. The user can scroll forward in the list or backward in the : :list, to the ends of the file if they so chose. To implement this, we : :have two files: one indexes the names in ascending alphabetical order, : :and the other indexes the names in descending alphabetical order. This is absolutly something one would want to do, but it is difficult with any relational database (not spesific to Informix). One thing you *might* be able to do is select into a scroll cursor based on the first characters the user entered and place that in a list on screen. It wouldn't give you the same behaviour, but it might suffice. What we do instead is let the user key in the whole name and use a phonetic key when querying the database. If the key is well made the name the users wants is most often in the data returned, and we let the user select it from a list with other data that enables this. There has reasently been a discussion about the metaphone algoritm for phonetic keys in this newsgroup that you might want to look into. : :I would guess that we do not want to try to duplicate this exactly as : :we move to Informix. The "forward" select and the "backward" select : :might each select hundreds of thousands of records - since I'm : :currently in a development environment and don't have the database : :loaded with that many records and can't test it, I'm guessing that : :response time in an online program using that strategy would be : :unacceptable. Yes, very. Not only will it take a long time, but also use a lot of diskspace. Nils.Myklebust@ccmail.telemax.no NM-data, Aasesvei 71, 1300 Sandvika, Norway My opinions are those of my company