AS/400 – Convert physical file with multi record to single record
This is AS/400 question, any idea to convert a multi-record physical file to a single record.
Example: [strong]FILE A[/strong] 000000 AAAAA 000000 BBBBB 000000 CCCCC 000000 DDDDD Convert to
[strong]FILE B [/strong]000000 AAAAABBBBBCCCCCDDDDD
Many thank ur idea.
Software/Hardware used:
AS/400
Example: [strong]FILE A[/strong] 000000 AAAAA 000000 BBBBB 000000 CCCCC 000000 DDDDD Convert to
[strong]FILE B [/strong]000000 AAAAABBBBBCCCCCDDDDD
Many thank ur idea.
Software/Hardware used:
AS/400
ASKED:
November 10, 2010 2:38 AM
UPDATED:
November 11, 2010 8:06 AM
Answer Wiki:
If I understand correctly, you have a PF with X number of records and you want to concatinate the value from one of the fields for all the records into one long charactger string and write that to a new file.
CRTPF &LIB/&NEWFILE RCDLEN(X * 5) thats the number of records in the source file time the number of bytes you want to keep for each record)
Write an RPG program to read the the source file and use SUBSTR to add the value from each record read to the end of the string.
On LR write the string to the new file.
To see all answers submitted to the Answer Wiki: View Answer History.
From CharlieBrowne’s assumption, you have:
Example:
FILE A
000000 AAAAA
000000 BBBBB
000000 CCCCC
000000 DDDDD
Convert to
FILE B
000000 AAAAABBBBBCCCCCDDDDD
Is that all you have to work with? Is there any other column that might help with ordering the rows? A sequence number, a timestamp or anything similar might do. I think it’s technically possible with SQL without such a column, but it’d be really ugly. I can guess that even RRN() might be useful if really needed, but a real piece of data could be much better.
BTW, when pasting into an editor such as this one, actual newlines may need to be added. Otherwise things just get strung together when they come back to a browser.
Tom
Thx CharlieBrowne & Tomliotta ur help..
There hasn’t been much help from us yet, but you’re welcome. Can you show more information about FILEA? Can you work with RPG? Do you have a preferred way to go forward?
Tom
For FILE A , it only have 1 field with length 5. I don’t think we can simply
CRTPF &LIB/&NEWFILE RCDLEN(X * 5) for FILE B and call a RPG pgm to output FILE B as it may hit file level check. Do you think so?
I hv an idea, but I think there is a limitation: create a temporary file (FILE C) with 10k length then call a RPG pgm to output FILE C. From RPG, we can know total length required then CRTPF &LIB/&NEWFILE RCDLEN(total length required) then CPYF *ADD FILE C to FILE B.
CLP -> RPG (Input file FILE A and output to FILE C 10k length)
-> CRTPF -> CPYF *ADD FILE C to FILE B
Do you have better idea other then this?
Thx.
I had thought, like CharlieBrowne, that FILEA had at least two fields. It looked like FILEA had one field that had numeric digits that acted like a key. You wanted the fields from the same key to be concatenated together into a single record, like a pivot table might be.
But now it sounds like you only have a single field and you want all records in FILEA to create only a single record in FILEB.
Is that true? Will FILEB have only one record when the process finishes? Do you know a maximum size for the record in FILEB? Does it matter what the order of the fields is?
Tom
“But now it sounds like you only have a single field and you want all records in FILEA to create only a single record in FILEB.”
Yes, correct.
“Is that true? Will FILEB have only one record when the process finishes? Do you know a maximum size for the record in FILEB? Does it matter what the order of the fields is?”
Yes, FILEB only one record when the process finished. There is no maximum size for FILEB, I simply give a 10k size. if FILE A more then 2000, sure it will be a problem. I don’t care about the order of the fields.