Possible to ignore case in ACCESS statement?

[Darren Reely]

Martyn,

With my distrust of user entry & database content, and my dislike of not 
using database indexes, I've come up with another solution. It involves 
no views or cursors either. But, it does rely on some trust of the 
database content, that the first word starts with an upper case letter 
and the remaining word is lower case. With this in mind, we should be 
able to cause an index to be used.

In this code I use only the first word in the ACCESS statement. This 
generates SQL with the common BETWEEN clause PH uses. To ignore the case 
of the string, I use the SELECT statement which does not get parsed into 
the generated SQL PH uses.

This is a technique I've only just come up with for this discussion. In 
other words, this code is a hack that will need review if considered for 
production.

Enjoy!

--------------------------------------------------------
screen ic

temp cn_upper varchar*30 reset at startup
temp cn_partial varchar*30 reset at startup

file OPS$DREELY.ic in mfg primary
access via client_id using client_id request client_id
access via client_name using cn_partial request client_name
select if cn_upper IS NULL  &
         OR NOT cn_upper IS NULL AND cn_upper = 
upshift(client_name[1:size(cn_upper)])
;; Use setenv DMAPIFIL <outputfilename> to see sql generated during run.

;; We want to truncate the cn_partial value used in generated sql
;; because we don't trust that the next word will be capitalized correctly.
;; This to help the database use an index. Never garranteed of course.
;; In Quick I'd likely loop through the string. This is a demo only.
define s1 num = index(client_name,' ')
define s2 num = index(client_name,'-')
define substr num = s1 if s1 < s2 or (s1 > 0 and s2 = 0 ) &
         else s2 if s2 > 0 &
         else size (trunc(client_name))

title "ingore case sample" centered
skip to 4
FIELD CLIENT_ID OF IC NULL VALUE NOT ALLOWED
FIELD CLIENT_NAME OF IC NULL VALUE NOT ALLOWED

procedure internal fetch_prep
begin
if 0 < index(client_name,"@")
then begin
         let cn_partial = trunc( UPSHIFT(client_name[1:1]) &
		+ DOWNSHIFT(client_name[2: substr - 1]) ) + "@"
         if 0 < index(cn_partial,'@@')
         then let cn_partial = cn_partial[1: size(cn_partial) - 1]
         let cn_upper = upshift(client_name[1: size(client_name) - 1])
         end
else begin
         let cn_partial = trunc( UPSHIFT(client_name[1:1]) &
		+ DOWNSHIFT(client_name[2: substr - 1])) + "@"
         let cn_upper = upshift(client_name)
         end
end


;; since I ran a compile with DETAIL LIST earlier,
;; I happen to know the path I'm looking for.
;; This would be risky in production code without
;; custom written PATH code.
procedure postpath
if path = 2
then do internal fetch_prep
else let cn_upper = null

build detail list
--------------------------------------------------------

Darren


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.
-----------------------------------