How to compose a SELECT() UDF 1

[KoenPiller]

Hi!
I am looking for a way to convert the Select commands you will find here into a UDF. The code works fine when I extend this with as many CASE in the DO CASE as the length of the given cSearch.Value which can be upto 40 characters, this would give a very long code listing. I get stuck with the "quotation marks".
To explain: cSearch is a restitricted character value with eventually ? wildcards. It selects from a dictionary (myFile) any words with the given letters in variable cSearch, so search for “aka” and you will get “aak”,”kaa” if that are correct words, search for “ak?” would give “aak”,”kaa”, and “afk”. field1= words as correctly written and field2=words without diactric letters e.g. all “ä” are “a” and other transformations.
Any smartfox around who could give me a hint how to write these 2 Selects into one or 2 UDF ?

[tt]LOCAL lnV,lnI,lnN2,lcNwrd,lcN
lnV=0
lnI=0
lcNwrd=""
STORE ALIAS() TO myTable
USE
USE (myTable) EXCLUSIVE
WITH ThisForm
DIME myArray(1)
FOR lnI=1 TO lnVar
lcN=SUBS(ALLTRIM(LOWE(.cSearch.Value)),lnI,1)
DO CASE
CASE lcN = "?"
LcN = ""
LnV = lnV+1 &&increment of search with number of ?wildcard
*CASE lcN = some other condition
*lcN = other Value
ENDCASE
lcNwrd = lcNwrd+lcN
ENDFOR
LcNwrd = ALLTRIM(lcNwrd)
LEN(lcNwrd) = LEN(lcNwrd)
**Select()#1
DO CASE
CASE LEN(lcNwrd) = 2
SELECT Field1 FROM myTable WHERE ;
AT(SUBS(lcNwrd,1,1),myTable.Field2)>0 AND ;
AT(SUBS(lcNwrd,2,1),myTable.Field2)>0 AND ;
LEN(ALLTRIM(myTable.Field2))= lnVar INTO CURSOR myC
CASE LEN(lcNwrd) = 3
SELECT Field1 FROM myTable WHERE ;
AT(SUBS(lcNwrd,1,1),myTable.Field2)>0 AND ;
AT(SUBS(lcNwrd,2,1),myTable.Field2)>0 AND ;
AT(SUBS(lcNwrd,3,1),myTable.Field2)>0 AND ;
LEN(ALLTRIM(myTable.Field2))= lnVar INTO CURSOR myC
*CASE LEN(lcNwrd) = 4
* and so on
ENDCASE
cLet1 = SUBS(myC.Field1,1,1)
cLet2 = IIF(LEN(ALLT(myC.Field1))>1,SUBS(myC.Field1,2,1),"&quot
cLet3 = IIF(LEN(ALLT(myC.Field1))>2,SUBS(myC.Field1,3,1),"&quot

** Select()#2
DO CASE
CASE LEN(ALLTRIM(LOWE(.cSearch.Value))) = 2
SELECT * FROM myC WHERE ;
BETW(OCCU(cLet1,myC.Field1),OCCU(cLet1,lcNwrd),OCCU(cLet1,lcNwrd)+lnV) AND ;
BETW(OCCU(cLet2,myC.Field1),OCCU(cLet2,lcNwrd),OCCU(cLet2,lcNwrd)+lnV) ;
INTO ARRAY.myArray
CASE LEN(ALLTRIM(LOWE(.cSearch.Value))) = 3
SELECT * FROM myC WHERE ;
BETW(OCCU(cLet1,myC.Field1),OCCU(cLet1,lcNwrd),OCCU(cLet1,lcNwrd)+lnV) AND ;
BETW(OCCU(cLet2,myC.Field1),OCCU(cLet2,lcNwrd),OCCU(cLet2,lcNwrd)+lnV) AND ;
BETW(OCCU(cLet3,myC.Field1),OCCU(cLet3,lcNwrd),OCCU(cLet3,lcNwrd)+lnV) ;
INTO ARRAY.myArray
*CASE LEN(ALLTRIM(LOWE(.cSearch.Value))) = 4
* and so on
ENDCASE
ENDWITH
USE
USE (myTable) EXCLUSIVE AGAIN[/tt][c/color]

 

[richlp]

I'm not sure I understand the rules you need to enforce, so here is my guess.

1. If lcNwrd does NOT contain a question mark then select the record if Field2 matches any arrangement of the letters in lcNwrd - that is

lcNwrd Acceptable Values
'aak' 'aak', 'aka', 'kaa'
'abc' 'abc', 'acb', 'bac', 'bca', 'cab', 'cba'

2. If lcNwrd DOES contain a question mark then select the record as if Field2 contains all the characters in lcNwrd. I'm assuming that if lcNwrd contains repeated characters then Field2 must contain at least that many of each character.

Whew......Assuming that all that is correct, try the following function

FUNCTION myTest
PARA tcField,tcPattern
pcLetters="abcdefghijklmnopqurstuvwxyz"
IF "?" $ tcPattern
  FOR I = 1 TO LEN(pcLetters)
    IF OCCURS(SUBSTR(pcLetters,I,1),tcField) < OCCURS(SUBSTR(pcLetters,I,1),tcPattern)
      RETURN .F.
    ENDIF
  ENDFOR
ELSE		
  FOR I = 1 TO LEN(pcLetters)
    IF OCCURS(SUBSTR(pcLetters,I,1),tcField) <> OCCURS(SUBSTR(pcLetters,I,1),tcPattern)
      RETURN .F.
    ENDIF
  ENDFOR
ENDIF
RETURN .T.

Your select statement would then be something like

SELECT field1 FROM Mytable WHERE MyTest(TRIM(field2),lcNwrd)

Please note that I've kept this to lower case only. You may have to make some adjustments is case is an issue.


Hope this helps (or someone posts someting better) ........Rich

 

[KoenPiller]

Hi Rich!
I had ad a third parameter tnLength = LEN(lcNwrd) and extended your SELECT() code with an 'OR LEN(tcField)#tnLength and it worked fantastic in testing. Even a lot faster than my 'direct' coding, specially on long combinations. However I am not yet preparred to hoist a flag reading "Methods and events cannot obtain nested procedures or class definitions" when I changed my procedure on the form with this UDF. Do you have an idea what and where I should change my form/procedure?
Thanks in advance.
Koen

 

[richlp]

Try making the UDF a custom method on the form and call it with

SELECT field1 FROM Mytable WHERE Thisform.MyTest(TRIM(field2),lcNwrd)

.......Rich

 

[KoenPiller]

Hi Rich,

You are 100% correct! Thanks a lot, learned again a lot. Koen