I have a peice of code below that has actually worked quite well except for one anomally. Ocassionally the script abends during the delete portion of the script. The column UR has a an index on it. I have noticed however that when I run the script to process hundreds of files without an index on UR, it never abends. Any ideas why an index on the column would cause the abend??
<$sqlq="select UR UsRec from " $tbname " where XD='S'">
<sum "" $sqlq>
<SQL $ret></SQL>
<$t1=(convert( 'now' , 'date' ))>
<LOOP $UsRec>
Processing $UsRec
<$sqlq="delete from " $tbname " where UR = $$UsRec">
<sum "" $sqlq>
<SQL MAX='1' $ret></SQL>
Don't know about the abend (someone else will answer ), but is there some reason that you're coding this this way instead of :
<$sqlq="delete from " $tbname " where XD='S'">
<sum "" $sqlq>
<sql ROW $ret>
Processing $UR
</sql>
Looping like you are would be pretty slow in comparison. Also, please don't forget to use the ROW or NOVARS clauses to any SQL statement where they are applicable. Doing otherwise wastes a lot of memory and consumes more CPU time needlessly.
actually, I didn't write it.. One of your people did a couple of years ago when my company contracted with them to do some of the work on our database. I have been meaning to re-write it to take advantage of the newer features of vortex but have not had time.
I'll have to find that person and have him/her thrashed soundly. It doesn't look like something we'd write though. Every version of Vortex we've ever released would be able to execute the code the way I suggested in my reply.
BTW: Everyone's gone for the day, so they will get to your question in the AM on Wed.
Which version of Texis are you running? Do you have an index on XD? The code that Bart suggested should work better, although neither should cause an abend. Does the abend occur reproducibly such that we could try it here?