>Hi,
>
>My question is Can an array which is created by a SELECT-SQL statement be refreshed with new data if the SELECT-SQL statement is re-ran, say from within a FOR loop? If not, how can this be done?
>
>FOR lnBrCount = 1 TO ALEN(laBranch,1)
> SELECT Loaninfo.* ;
> FROM Loancomm!loaninfo ;
> WHERE Loaninfo.cbranch = laBranch[lnBrCount] AND MONTH(Loaninfo.dcommdate) = lnBrCount AND YEAR(Loaninfo.dcommdate) = gnType ;
> GROUP BY 4 ;
> ORDER BY Loaninfo.cbranch ;
> INTO ARRAY vaTest
>ENDFOR
>
>I tried RELEASE vaTest after the FOR loop, so that the SELECT statement can recreate vaTest with the new data during the next instance of lnBrCount, but it seems that it doesn't redimension the array.
>
>Any suggestions are appreciated.
>
>Thank you,
Release after the loop or after the SQL ? If you don't release in loop and say first SQL returns 10 rows and others less than 10 then array would always have 10 rows (SQL redimensions the array only if size isn't enough to hold new rows - if SQL returns no rows array isn't created/redimensioned).
loaninfo.* doesn't sound to be a good choice with an SQL that has GROUP BY (VFP8 wouldn't even let it unless you set enginebehaviour to 70). It only helps to create an array with more elements (unless done on purpose to get last physical entries grouped by column 4).
Second your SQL sounds could be created w/o a loop and faster.
create cursor crsTemp (branch c(10),cDate c(6))
dimension laBranch[alen(laBranch,1),1]
append from array laBranch
update crsTemp set cDate = DTOC(DATE(gnType,recno(),1),1)
SELECT Loaninfo.* ;
FROM Loancomm!loaninfo li INNER JOIN crsTemp ct ;
ON li.cbranch = ct.branch AND ct.cDate = left(DTOC(li.dcommdate,1),6) ;
GROUP BY 4 ;
ORDER BY li.cbranch ;
INTO ARRAY vaTest
GROUP BY 4 is also questionable. Use fieldname instead. If structure changes column 4 might become column 3.
Cetin
