Oracle-L: Re: Client Search Info Needed

From: John Shaw <John.Shaw_at_correctionscorp.com>
Date: Fri, 31 Oct 2003 09:39:36 -0800
Message-ID: <F001.005D535E.20031031093936@fatcity.com>

In order to find all your clients with wild cards you would have to use a function like upper(lastname) like 'MCD%' - but you really don't want to be doing full table scans. You could create a function based to look at your data based upon the function ie: CREATE INDEX emp_name_index ON emp ( UPPER(ename)) ; As for finding alternative spellings of the last name you will porbably want to use a soundex function - you can use the default one in Oracle, it does work OK for basic functionality.
>>> tracy.rahmlow_at_aexp.com 10/30/2003 5:14:25 PM >>>

I am looking for an efficient solution to the following:

We intend to capture information about a client such as:

first name - John
last name - McDonald
phone numer - 222.222.2222
zip code - 43333
state - FL
client number - 123343

The names will be stored in mixed case for proper printing on client documents. The reps would like the flexiblity to enter the search criteria in a number of formats such as:

last name like mcdon* (wildcard) and first name = john
client number = 123343 (note: some clients do not always have their client number handy so it can not be the only available search mechanism)
last name = mac gregor (and locate both macgregor and mac gregor)
last name = kinney-jones (and locate both kinney-jones and kinney jones)

How many indexes and of what type are required? Does the leading the column of an index have to be specified for the index to be used? I thought I remember hearing that that was a limitation of an older release, but that is no longer the case with 8 and up. Are there any white papers available that address the topic of client search and best practices?

Thanks for your help!!

American Express made the following
annotations on 10/30/2003 04:11:07 PM

"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Tracy Rahmlow
  INET: tracy.rahmlow_at_aexp.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Shaw
  INET: John.Shaw_at_correctionscorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 31 2003 - 11:39:36 CST