Update For Each Records

jcant

New Member
First of all I'm sorry for the confusing title. Summarizing my need, what I need is that in the example below the last-of of break by is the record of id 3, not the record of id 4.

The record of id 4 should not appear.

What I think is that when the break by clause is used, all records are thrown into memory, and if I update some record brought in for each, the where filter is not applied.

My name is Jonas and I'm Brazilian.

Code:
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.
 

Stefan

Active Member
I think this is a result of all your records being sorted when the for each starts since there is no index that supports your break by.
If you add an index on customer, the preliminary sorting is not required and you will not see record 4.
 

Cringer

ProgressTalk.com Moderator
Staff member
Whilst I think Stefan's solution will work, one has to add that this is not a particularly good idea. Updating records within a query in order to control the records returned by the query is not a way to provide code that is easy to maintain in future.
 

jcant

New Member
I think this is a result of all your records being sorted when the for each starts since there is no index that supports your break by.
If you add an index on customer, the preliminary sorting is not required and you will not see record 4.
Thanks for the answer. In fact, adding the index solved the problem.
 

jcant

New Member
Whilst I think Stefan's solution will work, one has to add that this is not a particularly good idea. Updating records within a query in order to control the records returned by the query is not a way to provide code that is easy to maintain in future.
It really is not the best solution, but the program I'm maintaining does not allow me another alternative at the moment. Thanks for the answer.
 
Top