Joined: 10 Mar 2017 Posts: 13 Topics: 2 Location: Boston
Posted: Mon Mar 13, 2017 3:47 pm Post subject: Reformatting output file by including new field in Joinkeys
Hello,
I am having an issue in understanding the depth of JOINKEYS. I am trying to compare and merge two input files (each file having different layout) which are in SORTED order and populate the new output file with below condition.
a. Match fields fld#1, fld#2 (both file 1 and 2) and fld#3 (file1) with fld#4(file 2). If all three fields matching in both files, write a record from FILE 1 ONLY and discard file 2 record.
b. If no records matches, then write file 1 record as well file 2 records.
c. While writing file 2 records, we need to include all fields except FLD#3. Also new field should be included at end of the record with value '0.00' while writting records from file 2 to match the record layout of file 1.
111 991 Y 12.10 A 10 12.70
222 992 N 11.10 A 10 17.70
333 993 Y 8.10 D 11 11.88
333 996 Y 18.10 D 11 0.00
444 994 N 2.23 D 12 6.12
555 995 Y 18.12 A 12 19.39
666 997 N 21.23 D 12 0.00
777 998 Y 38.12 A 12 0.00
Is it possible to achieve it using JOINKEYS and REFORMATTING? Kindly help. Thanks in advance. _________________ B@L@
Joined: 10 Mar 2017 Posts: 13 Topics: 2 Location: Boston
Posted: Tue Mar 14, 2017 4:00 pm Post subject:
Hi kolusu,
Your as usual amazing. Thanks for the solution. I wonder how you see things simple always.
I also would like to take this opportunity in understanding the solution you provided.
Code:
JOIN UNPAIRED
This is as same as JOIN UNPAIRED F1, F2 which will extract the paired records (when JOINKEYS matches F1 and F2 record) from F1 ONLY along with unpaired records from F1 and F2. This is my requirement.
Code:
REFORMAT FIELDS=(F1:1,80,?,F2:1,80)
Reformatting fields with '?' indicates complete file content from files (F1 and F2) and 1-byte indicator at end of the file to show if key present in F1 or F2.
We build the output file by modifying the records for file F2 by checking the 1 byte indicator value as '2'. It will add '0.00' in the position 26. Kindly correct me if my understanding was right. Highly appreciate your time and help. Thanks again for the simple solution. Nice learning. _________________ B@L@
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
Posted: Tue Mar 14, 2017 4:39 pm Post subject:
balaryan wrote:
This is as same as JOIN UNPAIRED F1, F2 which will extract the paired records (when JOINKEYS matches F1 and F2 record) from F1 ONLY along with unpaired records from F1 and F2. This is my requirement.
Yes it is the same. You can find the detailed description here
Reformatting fields with '?' indicates complete file content from files (F1 and F2) and 1-byte indicator at end of the file to show if key present in F1 or F2.
The reformat statement is building the records as follows
Code:
----------------------------------------------------------------
|BYTES 1 THU 80 FROM FILE-1 | IND ? | BYTES 1 THU 80 FROM FILE2 |
----------------------------------------------------------------
DFSORT Application Programming Guide wrote:
? indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:
So for the matched records(IND = 'B'), you wanted the records from file-1 itself and the contents of File-1 are already in the first 80 bytes, so we do not need to re-arrange the data. The IFOUTLEN=80 will drop off any thing beyond position 80 (this has the indicator and file-2 data)
Similarly the unpaired file-1 records (IND = '1') you wanted the records from file-1 as is and the contents of File-1 are already in the first 80 bytes, so we do not need to re-arrange the data. The IFOUTLEN=80 will drop off any thing beyond position 80 (this has the indicator and 80 spaces)
So for the unpaired file 2 records(IND = '2'), the data is at postion 82 (file80+1 byte ind =81), So we pull the desired data from position 82 onward and remove the fields we do not want and at the same time also put the default values of 0.00
So we use a single IFTHEN statement and build the unpaired FILE-2 records. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 10 Mar 2017 Posts: 13 Topics: 2 Location: Boston
Posted: Tue Mar 14, 2017 5:48 pm Post subject:
Thanks Kolusu for the detailed information which helped me in understanding the concept of JOINKEYS. Appreciate your help and time in providing valuable info. _________________ B@L@
Joined: 10 Mar 2017 Posts: 13 Topics: 2 Location: Boston
Posted: Thu Mar 16, 2017 11:02 am Post subject:
Hi Kolusu,
Apologies for using the same topic to post my query which was based on the initial problem. Is it possible to sort the output file as a result of this JOINKEYS with two fields in ASC and other fields in DESC order in the same step? _________________ B@L@
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
Posted: Thu Mar 16, 2017 11:26 am Post subject:
balaryan wrote:
Hi Kolusu,
Apologies for using the same topic to post my query which was based on the initial problem. Is it possible to sort the output file as a result of this JOINKEYS with two fields in ASC and other fields in DESC order in the same step?
Yes it is possible. Replace the OPTION COPY statement under SYSIN to SORT FIELDS=(....) and that will perform a SORT after the JOIN operation is completed.
balaryan wrote:
I tried searched in IBM DFSORT manuals and no luck in finding any information.
Did you look at this ? Read under the section "Main Task for Joined Records"
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum