Possible to ignore case in ACCESS statement?
Thomson, Martyn EDUC:EX
Martyn.Thomson@gov.bc.ca
Wed, 4 May 2005 11:04:05 -0700
Just got back to this - thanks for your suggestions guys.
After some experimentation, I found a method to allow the user to enter a
substring of client name in any case (followed by "@"), and have the mixed
case value that's on the database displayed back in the same field. For
those interested, here's the code -
In the database -
create view CLIENT_VIEW
(CLIENT_ID,
CLIENT_NAME_UPSHIFT,
CLIENT_NAME)
as select
C1.CLIENT_ID,
upper(C2.CLIENT_NAME),
C2.CLIENT_NAME)
from CLIENT C1, CLIENT_NAMES C2
where (C1.CLIENT_ID = C2.CLIENT_ID)
In the screen -
file client_view IN mydb PRIMARY transaction query occurs 1 cache
access via client_id request client_id orderby client_id
access via client_name_upshift request client_name_upshift orderby
client_name
field CLIENT_ID OF client_view
field CLIENT_NAME_UPSHIFT OF client_view
procedure input CLIENT_NAME_UPSHIFT
begin
let FIELDTEXT = UPSHIFT(FIELDTEXT)
end
procedure output CLIENT_NAME_UPSHIFT
begin
; to ensure value not displayed in uppercase on action NEXT DATA
let FIELDTEXT = CLIENT_NAME of CLIENT_HIST_VERY_FULL_VIEW
end
procedure postfind
begin
;to prevent the "Data has been changed" msg when exiting (even though it's a
non-updatable view!)
display CLIENT_NAME_UPSHIFT of CLIENT_HIST_VERY_FULL_VIEW &
from CLIENT_NAME of CLIENT_HIST_VERY_FULL_VIEW
end
Martyn
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com]On Behalf Of Joe Boyle
Sent: Tuesday, May 03, 2005 7:05 AM
To: 'brian_matthewsbrian matthews'; powerh-l@lists.sowder.com
Subject: RE: Possible to ignore case in ACCESS statement?
in order to manage generic entries I think that the code is mixed up and
should be as below, I can't see a need for the temp item,
sql in PR declare myview cursor for &
select client_name , ...itemn from client_view
access myview request client_name using client_name &
via client_name orderby client_name
procedure input client_name
begin
if 0.5 lt size(truncate(fieldtext))
then &
let fieldtext = &
upshift(fieldtext[1:1]) + truncate(fieldtext [2:50])
end
Regards, Joe.
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of brian_matthewsbrian
matthews
Sent: 03 May 2005 03:26
To: Martyn.Thomson@gov.bc.ca; powerh-l@lists.sowder.com
Subject: RE: Possible to ignore case in ACCESS statement?
in fact, its probably best to seperate the where clause from the declare
cursor and code it into an access statement as below, this also takes care
of generic retrieval by generating the ranged option in the find proc.
temporary t_client_name varchar*30
sql in PR declare myview cursor for &
select itemlist from client_view
cursor myview primary occurs 20
access request t_client_name where client_name= :t_client_name orderby
client_name
procedure input client_name
begin
if 0.5 lt size(truncate(fieldtext))
then &
let fieldtext = &
upshift(fieldtext[1:1]) + truncate(fieldtext [2:50])
end
P.S. personally I'd leave it as a file statement
regards Bri,
>From: "brian_matthewsbrian matthews" <brian_matthews_bmw@hotmail.com>
>To: robeconsult@sbcglobal.net, powerh-l@lists.sowder.com
>Subject: RE: Possible to ignore case in ACCESS statement?
>Date: 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.
>> >
>> >
>>_________________________________________________________________
>> > MSNR 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=h
ttp://hotmail.com/enca&HL=Market_MSNIS_Taglines
>> >
>> > Start enjoying all the benefits of MSNR 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
>
>--
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>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.
_________________________________________________________________
Winks & nudges are here - download MSN Messenger 7.0 today!
http://messenger.msn.co.uk
--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.