How can i use FOR each, going through the table for one of the fields ascending in OpenEdge?

Question

So for example i have a table with field "sub_id" with is not sorted ascending or descending. I want to do FOR EACH from the smallest sub_id to biggest, how can i do that?

Answer 1

You should use the keyword BY. If the field you're sorting by isn't indexed this might be slow and consume system resources. This will be most apparent on a large table.

/* Smallest to largest */
FOR EACH tablename NO-LOCK BY tablename.sub_id:
  /* Do something */
END.

/* Largest to smallest sub_id */
FOR EACH tablename NO-LOCK BY tablename.sub_id DESCENDING:
  /* Do something */
END.

/* With a WHERE clause */
FOR EACH tablename NO-LOCK WHERE tablename.field = "something" BY tablename.sub_id:
  /* Do something */
END.

If you want to change data (update, assign etc) NO-LOCK as above won't work. NO-LOCK is for read-only operations (like displaying data). EXCLUSIVE-LOCK is for updates/deletes etc.