Best practice for Select/Update?

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
Annemiek
Posts: 5
Joined: Thu Oct 10, 2024 10:49 pm

Best practice for Select/Update?

Post by Annemiek » Tue Jan 14, 2025 12:24 am

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.

kno_dk
Posts: 207
Joined: Tue Feb 23, 2016 12:00 am

Re: Best practice for Select/Update?

Post by kno_dk » Tue Jan 14, 2025 1:35 am

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.

User avatar
Dino
Posts: 442
Joined: Fri Jul 19, 2019 7:49 am
Location: Robbinsville, NC
Contact:

Re: Best practice for Select/Update?

Post by Dino » Tue Jan 14, 2025 5:05 am

Or:

Code: Select all

Select *all from_file(myfile) with_key(new)
Execute Subroutine(Process)
#STATUS := 'PROCESSED'
Update #STATUS in_file(myfile)
EndSelect

Post Reply