>>Hi all,
>>
>>I having some problem of copying records from one table to another.
>>I did the following:
>>
>>use table1
>>
>>scan for
>> do case
>> case f = 1
>> e = 6
>> otherwise
>> endcase
>> scatter fields like f_name, e to array
>> use table2
>> append blank
>> gather from array fields f_name, e
>>endscan
>>
>>The result I'm getting in the Table2 is that it ONLY copy one record from
>Table1 to Table2. And I know there are more than one records that could
>satisfy the scan for statement.
>>Another problem I had is that the context of the field in Table2 is all
>mess up. Is there anyway to copy fields from one table to another without
>mess up the order. (I thought the scatter and gather command can be use to
>match specific fields that I want copy over)
>
>Sam:
>
>If your code above is the actual code, you have neglected to reselect table
>1 before just the endscan; you are still in table 2 so you're only getting
>one record in table 1 processed. You must always be selected to the
>scanned table when the loop iterates.

It seems worse than that: there it says "use table2", and it's still in the same workarea, so it closes the initial table, opens table2, appends one record and exits the loop. Scan...EndScan loop implicitly reselects the table it was run on upon every iteration. It's good to know it doesn't break down if the table gets closed in the meantime :) (never tried that, really).

As for the solution:

use table2
sele 0
use table1

scan for ...some condition...
scatter fields like f_name, e to array
do case
case f = 1
array(2) = 6
otherwise
endcase
insert into table2 from array
endscan
select table2

Now look at it. I've put array(2) = 6 instead of e=6 because e=6 really means m.e=6, i.e. it fills a memory variable e with a value of 6, and we don't use that variable anywhere, so it's useless. Since e is the second field we've scattered, I've stored the 6 to the second element of the array.

This kind of solution (scattering some fields to an array and modifying the values in it) is rather kludgy, because it refers to particular positions in the array. The next change of the list of fields scattered will make a nice mess of it. Just try to add another field before f_name in scatter, and it begins: it will either store 6 into a wrong field in table2, or it will break if that field is not numeric.