For Each with Where and Break By - Forum - OpenEdge Development - Progress Community
 Forum

For Each with Where and Break By

This question is answered

In the code example below, why does the id = 4 record continue to appear, knowing that in the where there is a clause deleting records with added <> "S"?

If I remove the break by clause, then the record does not appear, but I need break by to use the last-of, and I need the last-of to be true in the id = 3 record.

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.

Verified Answer
  • You don't have an index definition in your temp-table.  Try adding "index added added".

All Replies
  • You don't have an index definition in your temp-table.  Try adding "index added added".

  • That's it. Thanks!

  • , while that may be the accepted answer, could you elaborate on why adding an index solves the op's problem?

  • It has to with needing to sort the data before hand - see answers in the same question at www.progresstalk.com/.../

  • 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:

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

    end.

    disp tt-test.id

    tt-test.added

    last-of(tt-test.customer).

    end.

    Patrice

  • 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".

    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.