Macro Substitution, Partial Field Name

[SleepDepD]

Could I somehow use the & command for the following?

** "tcType" could be "pick" or "drop"
** the following is kind of pseudo-code
REPLACE first.tcType+"name" WITH ALLTRIM(second.name) ;
,first.tcType+"address1" WITH ALLTRIM(second.address1) ;
,first.tcType+"address2" WITH ALLTRIM(second.address2)
IN "first"
** There'll be additional "FieldName1 WITH eExpression1", but you get the idea

As you can see, I have 2 tables and multiple fields which all have the same prefix.

I appreciate the assistance.

 

[jimstarr]

I think the following syntax will work:

REPLACE first.&tcType.name WITH ALLTRIM(second.name)


Jim

 

[ramani]

Hi

SELECT first
REPLACE EVAL(tcType+'name') WITH ALLTRIM(second.name) ;
...



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[SleepDepD]

Worked great! Thanks a lot.

 

[Olaf Doschke]

Hi ramani,

You've made almost the right suggestion for a performance gain, but EVAL() is one step to much:

Example: tcType = "sure"
Then Eval(tcType+"sure") is equal to Eval("surename").
If first.surename exists and the value is "ramani", then:
Replace eval("surename") WITH ... is equal to Replace ramani WITH ...,
which won't of course work!

But here is a little benchmark of what works and how fast it is:

#Define cnIerations 10000
LOCAL lcDBC,lcDBF

CD (SYS(2023))

lcDBC = SYS(2015)
CREATE DATABASE (lcDBC)
lcDBF = SYS(2015)
CREATE TABLE (lcDBF) (cMainAdress c(50), cSecondaryAdress c(50))
INSERT INTO (lcDBF) VALUES ("Start1","Start2")

LOCAL ARRAY laFieldnameparts[2]
laFieldnameparts[1]="Main"
laFieldnameparts[2]="Secondary"

LOCAL lnT1, lnT2, lnCount, lnFieldcount, lcFieldnamepart

CLEAR && Screen
SET TALK OFF 
lnT1 = SECONDS()
SELECT (lcDBF)
FOR lnCount = 1 TO cnIerations
    FOR EACH lcFieldnamepart IN laFieldnameparts
        Replace ("c&lcFieldnamepart.Adress") WITH "Test1" 
    Endfor 
ENDFOR lnCount
lnT2 = SECONDS()
? "Evaluation by Macrosubstitution combined with ():", lnT2-lnT1

lnT1 = SECONDS()
SELECT (lcDBF)
FOR lnCount = 1 TO cnIerations
    FOR EACH lcFieldnamepart IN laFieldnameparts
        Replace c&lcFieldnamepart.Adress WITH "Test2" 
    Endfor 
ENDFOR lnCount
lnT2 = SECONDS()
? "Evaluation by Macrosubstitution:", lnT2-lnT1


lnT1 = SECONDS()
SELECT (lcDBF)
FOR lnCount = 1 TO cnIerations
    FOR EACH lcFieldnamepart IN laFieldnameparts
        Replace ("c"+lcFieldnamepart+"Adress") WITH "Test3" 
    Endfor 
ENDFOR lnCount
lnT2 = SECONDS()
? "Evaluation by ():", lnT2-lnT1

*!* Does not work, because Eval("c"+lcFieldnamepart+"Adress") = "Test3", 
*!* and field Test3 does not exist! Eval is one step too much!!!
*!*	lnT1 = SECONDS()
*!*	SELECT (lcDBF)
*!*	FOR lnCount = 1 TO cnIerations
*!*	    FOR EACH lcFieldnamepart IN laFieldnameparts
*!*	        Replace Eval("c"+lcFieldnamepart+"Adress") WITH "Test4" 
*!*	    Endfor 
*!*	ENDFOR lnCount
*!*	lnT2 = SECONDS()
*!*	? "Evaluation by Evaluate():", lnT2-lnT1
SET TALK ON

Bye, Olaf.

 

[Olaf Doschke]

Hi again,

In this special case, Execscript is even faster. Add this as Test5:

lnT1 = SECONDS()
SELECT (lcDBF)
LOCAL lcScript, lcReplace
lcReplace = "Replace "
FOR EACH lcFieldnamepart IN laFieldnameparts
    lcReplace = lcReplace + "c"+lcFieldnamepart+"Adress WITH 'Test5',"
ENDFOR
lcReplace = LEFT(lcReplace,LEN(lcReplace)-1)
TEXT TO lcScript TEXTMERGE NOSHOW 
FOR lnCount = 1 TO cnIerations
   <<lcReplace>>
ENDFOR lnCount
ENDTEXT
EXECSCRIPT(lcScript)
lnT2 = SECONDS()
? "Evaluation by EXECSCRIPT():", lnT2-lnT1

Of course it's not very usefull to execute the same Replace 10000 times and if you change cnIterations to 1 this will take quite long compared to the other tests, because not only a part of the replace but the outer loop has to be compiled.
Also the time needed would then not be accurately measureed with seconds(), as seconds() does only change about every 0.05 seconds!

You may get the idea, how execscript() could speed up prepared loops!

Bye, Olaf.