Hi Alan
Yes it only seems to be when summing to variables. Changing it to an array would necessitate further changes elsewhere. I have changed the code to allow a maximum of 32 and if it exceeds 32 I construct another second command to deal with the rest of the fields/variables.
Paul
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Alan Bourke Sent: 22 November 2019 13:49 To: profoxtech@leafe.com Subject: Re: Fatal error issuing CALCULATE command
Sent by an external sender ------------------------------
There definitely seems to be a limit of 32 as you say. Summing into an array as per below works for > 32 fields but I don't know if that would help.
#DEFINE _fields 35 #DEFINE _rows 100 Clear =RAND(-1) CREATE CURSOR crsTest (id i) cCalcCmd = [CALCULATE ] cCalcCmd2 = [ SUM ] cCalcCmd3 = "" cTemp = "" cInsertFields = [] cInsertValues = [] cVariables = [] FOR x = 1 TO _fields cField = "Field"+TRANSFORM(x) ALTER table crsTest ADD COLUMN (cField) I cCalcCmd = cCalcCmd + "SUM("+cField+")," cCalcCmd2 = cCalcCmd2 + cField + "," cTemp = cTemp + cField + "," cInsertFields = cInsertFields + ","+cField cInsertValues = cInsertValues + ","+TRANS(INT(RAND()*100)) cVariables = cVariables + "lc"+cField+"," NEXT cInsertCmd = [INSERT INTO crsTest (ID ] + cInsertFields +[) VALUES (0]+cInsertValues+')' FOR X = 1 TO _rows &cInsertCmd Next lcVars = Left(cVariables,Len(cVariables)-1) cCalcCmd = Left(cCalcCmd,Len(cCalcCmd)-1) + " To " + Left(cVariables,Len(cVariables)-1) &&+ "AVG(Field1)" cCalcCmd2 = Left(cCalcCmd2,Len(cCalcCmd2)-1) + " To " + Left(cVariables,Len(cVariables)-1) cTemp = Left(cTemp,Len(cTemp)-1) *? cCalcCmd *? cCalcCmd2 ? cTemp ? lcVars =StrToFile(cCalcCmd + Chr(13) + Chr(13) + cCalcCmd2, "c:\temp\cmd.txt") ? Len(cCalcCmd2) *&cCalcCmd *&cCalcCmd2 Sum &cTemp to array laTots List Memory like laTots Return
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Fri, 22 Nov 2019, at 12:59 PM, Paul Newton wrote:
No Alan - the following code (adapted from Eric) demonstrates exactly what I am doing:
#DEFINE _fields 35 #DEFINE _rows 100 =RAND(-1) CREATE CURSOR crsTest (id i) cCalcCmd = [CALCULATE ] cInsertFields = [] cInsertValues = [] cVariables = [] FOR x = 1 TO _fields cField = "Field"+TRANSFORM(x) ALTER table crsTest ADD COLUMN (cField) I cCalcCmd = cCalcCmd + "SUM("+cField+")," cInsertFields = cInsertFields + ","+cField cInsertValues = cInsertValues + ","+TRANS(INT(RAND()*100)) cVariables = cVariables + "lc"+cField+"," NEXT cInsertCmd = [INSERT INTO crsTest (ID ] + cInsertFields +[) VALUES (0]+cInsertValues+')' FOR X = 1 TO _rows &cInsertCmd NEXT cCalcCmd = Left(cCalcCmd,Len(cCalcCmd)-1) + " To " + Left(cVariables,Len(cVariables)-1) &&+ "AVG(Field1)" ? cCalcCmd &cCalcCmd Return
Paul
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Alan Bourke Sent: 22 November 2019 12:55 To: profoxtech@leafe.com Subject: Re: Fatal error issuing CALCULATE command
Sent by an external sender
Paul
Do you mean you are doing this?
calculate sum(field1 + field2 + ... + field32) to lnTotal
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]