How about some performance analysis?
Michael's solution does a resident load for every single row of the table. A million rows means a million loads X a million rows. That would be impossibly slow, and it would create a HUGE log file. It took 48 seconds to load just 5000 rows on my machine:
Data:
LOAD
recno() as ID
,recno() as Row // separate because an ID wouldn't normally match the row number
,ceil(rand()*10) as F1
,ceil(rand()*10) as F2
,'F1 ' & pick(ceil(rand()*4),'+','-','*','/') & ' F2' as Calc
AUTOGENERATE 5000
;
LET R=peek('Row');
FOR r=1 TO $(R)
LET E=peek('Calc',$(r)-1,'Data');
Tab:
LOAD ID, F1, F2, Calc, $(E) as Result
RESIDENT Data
WHERE Row=$(r);
NEXT r
DROP TABLE Data;
I can't test Ralf's solution because I'm not going to build a database. Again, for a million rows, there are a million loads X a million rows. HOWEVER, those loads are just a quick primary key read on a database. It SHOULD perform reasonably, but in my opinion puts an unnecessary load on the database management system, plus again would create a huge log file.
I'm betting I can crash my pick(match()) solution by adding a + recno() to the load and going to enough rows. If it doesn't crash, it'll at least get slow based on the gigantic pick(match()) I'll be generating. OK, it was still working at 100,000 rows, each with a unique calculation, but it took 142 seconds to load. There MAY be a point where it crashes, but it becomes impractically slow before it gets to that point. It might be a workable solution for some data sets (data sets with a fairly limited number of calculations), but it's not what I'd choose as a generic solution.
Data:
LOAD
recno() as ID
,ceil(rand()*10) as F1
,ceil(rand()*10) as F2
,'F1 ' & pick(ceil(rand()*4),'+','-','*','/') & ' F2 + ' & recno() as Calc
AUTOGENERATE 100000
;
Calcs:
LOAD
concat(chr(39) & Calc & chr(39),',',CalcSequence) as CalcNames
,concat( Calc ,',',CalcSequence) as Calcs
;
LOAD
recno() as CalcSequence
,text(fieldvalue('Calc',recno())) as Calc
AUTOGENERATE fieldvaluecount('Calc')
;
LET vCalcs = 'pick(match(Calc,' & peek('CalcNames') & '),' & peek('Calcs') & ')';
DROP TABLE Calcs;
LEFT JOIN (Data)
LOAD
ID
,$(vCalcs) as Result
RESIDENT Data
;
And finally we have Stefan's solution, modified by me to generate the required replace() statement automatically. Even with each calculation being unique, it loads a million rows in 13 seconds, two million rows in 25 seconds, and three million rows in 37 seconds. It's fast and looks to be o(n), as I'd expect. Of what we've seen so far, I think it's the most practical solution for real world data sets.
Data:
LOAD
recno() as ID
,ceil(rand()*10) as F1
,ceil(rand()*10) as F2
,'F1 ' & pick(ceil(rand()*4),'+','-','*','/') & ' F2 + ' & recno() as Calc
AUTOGENERATE 3000000
;
LET vCalcs = repeat('replace(',nooffields('Data')) & 'Calc';
FOR I = 1 TO nooffields('Data')
LET vCalcs = vCalcs & ',' & chr(39) & fieldname(I,'Data') & chr(39) & ',' & fieldname(I,'Data') & ')';
NEXT
LEFT JOIN (Data)
LOAD
ID
,evaluate($(vCalcs)) as Result
RESIDENT Data
;
).