Possible to ignore case in ACCESS statement?
brian_matthewsbrian matthews
brian_matthews_bmw@hotmail.com
Tue, 03 May 2005 00:26:25 +0000
same for Oracle and presumably OracleRDB, but if you upshift the 1st char in
the input proc the UPPER function can be removed from the cursor.
To simulate the use of @ generic retrieval char you will need syntax along
the lines of 'where column like :truncate (field) + '%' ' or 'where column
>= :truncate (field) '
regards Bri,
>From: Robert Edis <robeconsult@sbcglobal.net>
>To: PowerHouse List <powerh-l@lists.sowder.com>
>Subject: RE: Possible to ignore case in ACCESS statement?
>Date: Mon, 2 May 2005 16:25:41 -0700 (PDT)
>
>SQL Server will do a table scan if a function is used
>in the select logic where clause.
>
>Blue
>
>--- Peter Bateman <pfbcs@hotmail.com> wrote:
>
> > 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*.
> >
> > --
> > = = = = = = = = = = = = = = = = = = = = = = = = = =
> > = =
> > 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.
> >
>
>--
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>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.
_________________________________________________________________
Use MSN Messenger to send music and pics to your friends
http://messenger.msn.co.uk