Thanks for the performance tip. Yes, your suggestion will improve
performance and will make big difference if tables involved are very
large. Check for "a.umnum = 0" in sub-query is unnecessary after adding it
to where clause for update. If it is removed from sub-query (both
places) in your final version of SQL, it will be helpful for a reviewer,
there will be less code to review :).
It is great that you are very observant and always ready to point out
problems in posts made by others. It will be also helpful, if you show
same enthusiasm in acknowledging problems in your posts after someone
points out the problem. See the link
Normally, it is not an issue even when someone does not acknowledge.
However, there is a tendency by some people on iSeries forums to rely on
information based on who wrote the post without verifying. Some people even
find it disrespectful to question posts made by iSeries veterans :( . Some
iSeries veterans do not want to acknowledge error in their analysis even
if it is proved beyond doubt that the analysis was incorrect. Some people
will rely on posts made by iSeries veterans even after someone pointed out
the problem with the post.