In some of our production code sqlcode 0 & -811 were handled together hoping that in case of multiple rows satisfies the condition the first row will be fetched in the host variable.
MOVE TABLE-DATA TO WS-TABLE-DATA
One of such code requires some tuning & when i added FETCH FIRST ROW there the o/p files didnt match.After buring few ATPs I found that conditions where multiple rows satisfied were unmatched.Previoulsy it was picking the 2nd record now the first one.
I asked this question to every senior members of my team & got so many different answers[first row,junk,last row,dont know,unpredictable] that made me think it may vary on some parameter & in our shop it definately returns 2nd row.Now the same thing happens in my new project.
So does it happen everwhere?can you guys do a little test & post the result here.Curious to know!!!!
If you are relying on -811 as an "existence check" there are many better ways to achieve this.
If this is a case of wanting one row returned, but you don't care which one, then use a CURSOR.
its not for existence checking.even if cursor was used are you telling me to fetch the cursor single time?may be FETCH FIRST ROW serve the situation better.
If the sqlcode of the SELECT is non-zero, do not use the value(s) in the host variables