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.
 

Patrice Perrot

New Member
Hi

Adding an index will solve the isuse in most of case .

But if the record is in the "packet" received it will not work ,

You can try the code below there is only 3 record in the Temp-Table instead of 4 and the update is on record "3" .

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 = 3 /* 4 */ :

assign b-tt-test.added = "S".

end.

disp tt-test.id

tt-test.added

last-of(tt-test.customer).

end.

Patrice
 

Patrice Perrot

New Member
Hi ,
A little more explanation on the word "packet" , i am not using it for the packet send/recived in this case.
When you are using a Break by / First-Of / Last-Of , your query know the "next" record to evaluate the "Last" , i am using the word "packet" for the records that your query "know".
I think it will be more understandable with the exemple below , on the second disp there is NO "LAST-OF".

Code:
DEF TEMP-TABLE tt-test
FIELD id AS CHAR FORMAT "X(5)"
FIELD customer AS INT FORMAT ">>9"
    FIELD info    AS CHAR FORMAT "X(35)"
INDEX i1 id customer  .

DEF VAR ii AS INT NO-UNDO.
DEF VAR jj AS INT    NO-UNDO.
DO ii = 97 to 99 :
DO jj = 1 to 4 :
CREATE tt-test .
ASSIGN tt-test.id = CHR(ii)
tt-test.customer = jj .
CASE jj :
WHEN 1 THEN tt-test.info = "First" .
WHEN 3 THEN tt-test.info = "Before last => After delete LAST" .
WHEN 4 THEN tt-test.info = "Last" .
END CASE .
END.
END.
FOR each tt-test BREAK BY id :
disp tt-test.id
tt-test.customer
tt-test.info FIRST-OF (id) LAST-OF (id) .
END.

PAUSE 5 .
DEFINE BUFFER b-del FOR tt-test .
FOR each tt-test BREAK BY id :
IF tt-test.customer = 3
THEN FOR EACH B-del WHERE b-del.id = tt-test.id
AND b-del.customer = 4 :
delete b-del .
    END.
    DISP tt-test.id     
tt-test.customer
tt-test.info FIRST-OF (id) LAST-OF (id) .
END.
 
Last edited by a moderator:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Hi Patrice,
Can you please enclose your code within CODE tags? Start with [ code ] and end with [ /code ] (eliminate the spaces to make them actual code tags).
Or use Insert | Code from the post editor toolbar.
Thanks.
 
Top