break by 'character field'

[frusty]

Hi,
I've got a for each with a 'break by'.
The 'by' is on a character field.
It works fine,but when the character field is longer than 176 characters you get an error 'index array to long'.
Does anybody know a workaround for this ?

 

[diegomigueles]

Hi frusty,

You should try this:

FOR EACH tablename NO-LOCK
BREAK BY SUBSTR(fieldname,1,175):

END.

 

[frusty]

hi,
thanks, but i've already tried that.
The problem is that sometimes the fields are different after these 175 characters. So, most of the time this method works but not always

 

[M-HT]

Hi,
how about this solution:
DEFINE VARIABLE slSort AS CHARACTER
VIEW-AS SELECTION-LIST SINGLE SORT
SIZE 1 BY 1 NO-UNDO.

DEFINE VARIABLE slRows AS CHARACTER
VIEW-AS SELECTION-LIST SINGLE
SIZE 1 BY 1 NO-UNDO.

DEFINE FRAME xxx slSort slRows.

DEFINE BUFFER buf FOR tab.

DEFINE VARIABLE iNum AS INTEGER NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER NO-UNDO.

ASSIGN
slSortELIMITER = CHR(7)
slRowsELIMITER = CHR(7)
.

FOR EACH tab NO-LOCK BREAK BY SUBSTR(ch,1,175):
IF FIRST-OF(SUBSTR(ch,1,175)) THEN DO:
ASSIGN
slSort:LIST-ITEMS = ?
slRows:LIST-ITEMS = ?
iNum = 0
.
END.

slSort:ADD-LAST(ch).
slRows:INSERT(STRING(ROWID(tab)),slSort:LOOKUP(ch)).
iNum = iNum + 1.

IF LAST-OF(SUBSTR(ch,1,175)) THEN DO:
DO iTmp = 1 TO iNum:
FIND FIRST buf WHERE ROWID(buf) = TO-ROWID(slRows:ENTRY(iTmp)) NO-LOCK.
/* default behaviour */
END.
END.
END.

A few notes about it:
1) it's slow
2) if two rows have the same value of ch it returns them in reverse order - this could be avoided by using another set of selection lists and moving the values to them one by one from last to first
3) it's limited by maximum length of slSort:LIST-ITEMS - this could be improved by sorting only SUBSTRING(ch, 176) ( could be also faster )
4) i think speed could be improved by checking whether ch is actually longer than 175 characters
5) none of the tab.ch can't contain chr(7) for obvious reasons

 

[Kenneth Chow]

Further divide possible??

FOR EACH tablename NO-LOCK
BREAK BY SUBSTR(fieldname,1,175)
BY SUBSTR(fieldname,176):

END.

 

[M-HT]

Hi Kenneth.
No it doesn't work if length(fieldname)>=197.

 

[Kenneth Chow]

MT, are u sure it doesn't work?

I can run this in procedure editor:

for each bg-job
break by substr(pgm-name,1,200)
by substr(pgm-name,200,200):
disp pgm-name.
end.

 

[M-HT]

Kenneth, I'm quite sure.
I can run this too:

DEFINE TEMP-TABLE tt
FIELD cField AS CHAR.

CREATE tt.
tt.cField = "a".
CREATE tt.
tt.cField = "b".

FOR EACH tt BREAK BY SUBSTRING(tt.cField,1,197) BY SUBSTRING(tt.cField,198):
DISPLAY tt.cField.
END.

But when I run this:

DEFINE TEMP-TABLE tt
FIELD cField AS CHAR.

CREATE tt.
tt.cField = FILL("a",197).
CREATE tt.
tt.cField = "b".

FOR EACH tt BREAK BY SUBSTRING(tt.cField,1,197) BY SUBSTRING(tt.cField,198):
DISPLAY tt.cField.
END.

it outputs this error:
** Index value is too long. (141)

 

[Kenneth Chow]

Yes, you're right M-HT.
It is the limitation of progress.