For Each with Where and Break By

Posted by jcant on 03-Jul-2019 17:43

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.

Posted by Rob Fitzpatrick on 03-Jul-2019 19:02

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

All Replies

Posted by Rob Fitzpatrick on 03-Jul-2019 19:02

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

Posted by jcant on 04-Jul-2019 13:49

That's it. Thanks!

Posted by Lieven De Foor on 02-Aug-2019 11:10

[mention:4b133177ec1e4b6f9a6a7832e6f29913:e9ed411860ed4f2ba0265705b8793d05] , while that may be the accepted answer, could you elaborate on why adding an index solves the op's problem?

Posted by Stefan Drissen on 02-Aug-2019 13:03

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

Posted by Patrice Perrot on 07-Aug-2019 15:04

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

Posted by Patrice Perrot on 08-Aug-2019 07:02

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.

This thread is closed