Hi folks,
I've been running into problems with a Select/Update loop. I've managed to figure out a solution, but I'm not really satisfied with it. I should think many of you have had to deal with the same situation, so I wonder what the best practice is.
Here's the situation: I need to select all records in a file with status 'NEW'; process those records one by one and after processing update the status to 'PROCESSED'.
This is what I did at first:
#STATUS := 'NEW'
Select *all from_file(myfile) with_key(#STATUS)
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect
Now this doesn't work; I read in the Tech notes that the Update messes up the results of the Select. The following alternative didn't do what I expected either:
Select *all from_file(myfile) where('STATUS = ''NEW''')
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect
I resorted to the ugly construction of:
Select *all from_file(myfile)
Continue if('#STATUS *ne ''NEW''')
etc.
But i'm not happy. Do you have suggestions how I could have done a better job?
Thanks,
Annemiek.
Best practice for Select/Update?
Re: Best practice for Select/Update?
Try to create a working field like #WRKSTATUS
#WRKSTATUS := 'NEW'
Select *all from_file(myfile) with_key(#WRKSTATUS)
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect
I think it is becaus you change your #status field to 'PROCESSED' , so the second time your read a record it is with 'PROCESSED' in the key field.
#WRKSTATUS := 'NEW'
Select *all from_file(myfile) with_key(#WRKSTATUS)
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect
I think it is becaus you change your #status field to 'PROCESSED' , so the second time your read a record it is with 'PROCESSED' in the key field.
Re: Best practice for Select/Update?
Or:
Code: Select all
Select *all from_file(myfile) with_key(new)
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect