Possible to ignore case in ACCESS statement?

[Peter Bateman]

Hi all:

  I believe for most databases the code:-

       select * from client_view &
         where upper(client_name) = :upper(t_client_name)

will result in a table scan as opposed to an index scan  you may want
to get rid of your index or change your index perhaps to a soundex column or
an upshifted client_name column.

Some databases allow you to index on a calculated column.

Regards,
Peter Bateman



>I was thinking along the lines of your 3rd suggestion ("Use a view that
>upper cases the client name..."), but could find no SQL function to shift
>the field to uppercase. My manuals were printed Dec 1991. Tried UPPER() and
>it works great!
>Thanks for the tip.
>Martyn
>
>-----Original Message-----
>From: Darren Reely [mailto:darren.reely@latticesemi.com]
>Sent: Friday, April 29, 2005 4:28 PM
>To: Thomson, Martyn EDUC:EX
>Cc: POWERHOUSE NEW (E-mail)
>Subject: Re: Possible to ignore case in ACCESS statement?
>
>
>I can think of three ways to do this.
>
>You could customize the POSTPATH procedure to adjust the client_name to
>begin with an upper case letter. The problem with this is, what if the
>user types "acme c@" but the database entry is "Acme Company".
>
>Use a cursor to upper or lower case both sides of the equation. This
>isn't optimal but is usable. I think you'd need to use a temporary value
>for the prompt field.
>
>Here is some untested/unverified code:
>
>    temporary t_client_name varchar*30 reset at startup
>    sql in PR declare myview cursor for &
>      select * from client_view &
>      where upper(client_name) = :upper(t_client_name)
>
>Use a view that upper cases the client name and then force the same for
>the entry on the view screen. Then just redisplay what you get back.
>Simple to maintain and you don't have to worry about cursor quirks of PH.
>
>Good luck.
>
>Darren
>
>P.S. I don't suggest you do "select *" unless you really want everything
>in a record. Only select what you'll be using, otherwise you'll take a
>performance hit.
>
>
>Thomson, Martyn EDUC:EX wrote:
> >>Hi,
> >>I have a simple inquiry screen that retrieves a view of client data by
> >>client Id or name. Most but all of the names are stored in title case.
> >>
> >>file Client_view IN PR PRIMARY transaction query occurs 1 cache ˇ
> >>access via client_id request client_id
> >>access via client_name request client_name orderby client_name
> >>
> >>Retrieval on client_name is generic so trailing wildcard "@" can be 
>used,
> >>but is there any way to ignore case? Currently the user has to remember 
>to
> >>capitalize the first letter.
> >>
> >>Martyn
> >>OpenVMS Alpha V7.3-1, OpenVMS Alpha V7.3-1; Powerhouse 7.10G1
> >>
> >
> >
>
>--
>
>-----------------------------------
>HELP STOP SPAM. STOP USING WINDOWS.
>-----------------------------------
>
>--
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.sowder.com
>Subscribe: "subscribe" in message body to powerh-l-request@lists.sowder.com
>Unsubscribe: "unsubscribe <password>" in message body to 
>powerh-l-request@lists.sowder.com
>http://lists.sowder.com/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.

_________________________________________________________________
MSNŽ Calendar keeps you organized and takes the effort out of scheduling 
get-togethers. 
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines 
  Start enjoying all the benefits of MSNŽ Premium right now and get the 
first two months FREE*.