Kbase P65893: Different record inside and outside FOR EACH loop
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/29/2011 |
|
Status: Verified
SYMPTOM(s):
Different record inside and outside FOR EACH loop
Using BREAK BY option
Without the BREAK BY option, the current record remains the same after executing LEAVE statement.
A code like shown below will display different current record inside the FOR EACH loop and outside after executing LEAVE statement. /*-------------------------------------------------*/
DEFINE VARIABLE i AS INTEGER NO-UNDO.
FOR EACH customer NO-LOCK WHERE
NAME BEGINS 'B'
BREAK BY NAME:
i = custnum.
LEAVE.
END.
MESSAGE i SKIP custnum
VIEW-AS ALERT-BOX INFO BUTTONS OK.
/*-------------------------------------------------*/
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
CAUSE:
This is expected behavior:
BREAK-BY sometimes causes a "look-ahead" so we can see whether the current LAST-OF, FIRST-OF conditions have changed. Because of the look-ahead, the buffering is different in the FOR EACH.
We make no attempt to document what happens when you leave or fall out of a break-by loop -- you are expected to complete it with only forward motion. Once you fall out, there may or may not be a record there depending on the type of BREAK-BY it was. Any repositioning with FINDs etc. will also produce completely unexpected results.
Anyone using BREAK-BY has to follow what is expected, i.e. run through all the records in forward order only, or use a manual version.
The record in the buffer with break-by will be "off-by-one" because of the look-ahead, unless there is sorting involved - you could get still different behavior if "BREAK-BY address" is used. Since there is no index on ADDRESS like there is on NAME, we are forced to do a sort and then we do not need the look-ahead on the index, and just look ahead into the sort list instead.
FIX:
A possible way to avoid this behavior will be to save the record rowid inside the loop and search again for the record outside the loop. Please see example below:
/*-------------------------------------------------*/
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE Rec# AS ROWID NO-UNDO.
FOR EACH customer NO-LOCK WHERE
NAME BEGINS 'B'
BREAK BY NAME:
i = custnum.
Rec# = ROWID(customer).
LEAVE.
END.
FIND FIRST customer WHERE ROWID(customer) = Rec# NO-LOCK.
MESSAGE i SKIP custnum
VIEW-AS ALERT-BOX INFO BUTTONS OK.
/*-------------------------------------------------*/