For Each with Where and Break By

Question

I couldn’t find a suitable title for my doubt, but come on... Consider the code snippet below as an example:

def temp-table tt-test
    field id       as int
    field customer as int
    field added    as char.

create tt-test.
assign tt-test.id       = 1
       tt-test.customer = 100
       tt-test.added    = "".
create tt-test.
assign tt-test.id       = 2
       tt-test.customer = 100
       tt-test.added    = "".
create tt-test.
assign tt-test.id       = 3
       tt-test.customer = 100
       tt-test.added    = "".
create tt-test.
assign tt-test.id       = 4
       tt-test.customer = 100
       tt-test.added    = "".

def buffer b-tt-test for tt-test.

for each tt-test
   where tt-test.added <> "S"
   break by tt-test.customer:

    if tt-test.id = 2 then
        for first b-tt-test
            where b-tt-test.id = 4:
            assign b-tt-test.added = "S".
        end.

    disp tt-test.id
         tt-test.added
         last-of(tt-test.customer).
end.

What problem I’m facing: the for each continues to carry the record of id = 4 and this record had the field added set to "S", and where of for each there is a condition tt-test.added <> "S", that is, by logic it should not appear.

What I need: in the case of the above code, I need the last-of(tt-test.customer) be the record of id = 3, since the record of id = 4 should no longer be read by for each.

Out of curiosity: withdraw the clause break by of for each, then the record of id = 4 is not read, but in return without the break by I can’t use the last-of.

Answer 1

What you need to know is that it would be impossible to have a LAST-OF or FIRST-OF without a pre-selection of the records. Hence record 4 appears with break by, because it was preselected even before the first loop iteration occurs. Ideally you would change the records that can be ahead in a loop and make a for each without restrictions to follow, then having two loops, or, if this process has to be done simultaneously to the display, add an if before the display.

If you just wanted an answer, you can stop reading. As a piece of advice, remember that the display (unless this is an output report other than the terminal) can take time to be viewed by the user, and since you are changing the log, Progress automatically upgrades the block to a transaction, to accommodate your assignment. You didn’t explain a lock, so Progress assumes SHARE-LOCK. If this were a physical table, Progress would hold the lock while the user is looking at the logs. This increases the chance of a Dead lock.

Even though you are working with temp-Tables, I would avoid these constructions to not happen later you end up doing the same with physical tables. Get used to writing blocks with explicit lock (NO-LOCK or EXCLUSIVE-LOCK, always), and define transactions in specific blocks. Try your best to make read iterations your own block with no-lock, and your updates inside blocks with exclusive-lock, and with explicit transactions (again, for temp-table in general you don’t need), these best practices will save you a lot of headaches in the future.