|
View previous topic :: View next topic
|
| Author |
Message |
ArunkumarGanesan
New User
Joined: 09 Jan 2020
Posts: 8
Location: USA
|
|
|
|
Hi,
i am trying to compare two FB datasets with same record length of 80 on 1,34 and override 35,6 only when matched without changing the record order.
I tried the below but the unmatched records are written at bottom.
F1: LRECL=80, FB
| Code: |
**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.
*
**************************************************
*MNTH START NEXT
*ID VL REGION ARA PRODCT SEQ SEQ
*==== == ====== === ====== ====== ======
MAY 12 400972 770 XXXXXA 000502 000000 SEEDS
MAY 12 400972 781 XXXXXB 000502 000000 SEEDS
MAY 12 400972 789 XXXXXC 000502 000000 SEEDS
MAY 12 400972 791 XXXXXD 000506 000000 SEEDS
MAY 12 400977 416 XAXAXA 001002 000000 SEEDS
MAY 12 400977 416 XAXAXB 701002 000000 SEEDS
MAY 12 400977 421 XAXAXC 201002 000000 SEEDS
MAY 12 400977 421 XAXAXD 501002 000000 SEEDS
MAY 12 400977 425 XAXAXE 201002 000000 SEEDS
MAY 12 400977 425 XAXAXF 501002 000000 SEEDS
MAY 12 400977 425 XAXAXG 001002 000000 SEEDS
MAY 12 400977 425 XAXAXH 701002 000000 SEEDS
MAY 12 400977 426 XAXAXI 201003 000000 SEEDS
MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS |
F2:
| Code: |
MAY 12 400972 770 XXXXXA 000502 001456
MAY 12 400972 781 XXXXXB 000502 004456
MAY 12 400972 789 XXXXXC 000502 001233
MAY 12 400972 791 XXXXXD 000506 000045
MAY 12 400977 416 XAXAXA 001002 000022
MAY 12 400977 416 XAXAXB 701002 000001
MAY 12 400977 421 XAXAXC 201002 034556
MAY 12 400977 421 XAXAXD 501002 003422
MAY 12 400977 425 XAXAXE 201002 000099
MAY 12 400977 425 XAXAXF 501002 007566
MAY 12 400977 425 XAXAXG 001002 196663
MAY 12 400977 425 XAXAXH 701002 000301
MAY 12 400977 426 XAXAXI 201003 000000
MAY 12 400977 426 XAXAXJ 501004 000000
|
SYSIN:
| Code: |
JOINKEYS FILE=F1,FIELDS=(1,34,A)
JOINKEYS FILE=F2,FIELDS=(1,34,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,?,F2:35,6)
OPTION COPY
OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),
BUILD=(1,34,82,6,41,40))
|
Output from above sort:
| Code: |
MAY 12 400972 770 XXXXXA 000502 001456 SEEDS
MAY 12 400972 781 XXXXXB 000502 004456 SEEDS
MAY 12 400972 789 XXXXXC 000502 001233 SEEDS
MAY 12 400972 791 XXXXXD 000506 000045 SEEDS
MAY 12 400977 416 XAXAXA 001002 000022 SEEDS
MAY 12 400977 416 XAXAXB 701002 000001 SEEDS
MAY 12 400977 421 XAXAXC 201002 034556 SEEDS
MAY 12 400977 421 XAXAXD 501002 003422 SEEDS
MAY 12 400977 425 XAXAXE 201002 000099 SEEDS
MAY 12 400977 425 XAXAXF 501002 007566 SEEDS
MAY 12 400977 425 XAXAXG 001002 196663 SEEDS
MAY 12 400977 425 XAXAXH 701002 000301 SEEDS
MAY 12 400977 426 XAXAXI 201003 000000 SEEDS
MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS
**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.
*
**************************************************
*MNTH START NEXT
*ID VL REGION ARA PRODCT SEQ SEQ
*==== == ====== === ====== ====== ======
|
Expected Ouptut:
| Code: |
**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.
*
**************************************************
*MNTH START NEXT
*ID VL REGION ARA PRODCT SEQ SEQ
*==== == ====== === ====== ====== ======
MAY 12 400972 770 XXXXXA 000502 001456 SEEDS
MAY 12 400972 781 XXXXXB 000502 004456 SEEDS
MAY 12 400972 789 XXXXXC 000502 001233 SEEDS
MAY 12 400972 791 XXXXXD 000506 000045 SEEDS
MAY 12 400977 416 XAXAXA 001002 000022 SEEDS
MAY 12 400977 416 XAXAXB 701002 000001 SEEDS
MAY 12 400977 421 XAXAXC 201002 034556 SEEDS
MAY 12 400977 421 XAXAXD 501002 003422 SEEDS
MAY 12 400977 425 XAXAXE 201002 000099 SEEDS
MAY 12 400977 425 XAXAXF 501002 007566 SEEDS
MAY 12 400977 425 XAXAXG 001002 196663 SEEDS
MAY 12 400977 425 XAXAXH 701002 000301 SEEDS
MAY 12 400977 426 XAXAXI 201003 000000 SEEDS
MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS |
Thanks in advance |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
Location: Bamberg, Germany
|
|
|
|
| Code: |
//SYSIN DD *
JOINKEYS F1=F1,FIELDS=(1,34,A)
JOINKEYS F2=F2,FIELDS=(1,34,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,?,F2:35,6,F1:100,8)
SORT FIELDS=(88,8,ZD,A)
OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),
BUILD=(1,34,82,6,41,40))
END
/*
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(100:SEQNUM,8,ZD))
END
/*
//JNF2CNTL DD *
END
/* |
Output:
| Code: |
000001 **************************************************
000002 * UPDATE AS REQUIRED FOR EACH RUN.
000003 *
000004 **************************************************
000005
000006 *MNTH START NEXT
000007 *ID VL REGION ARA PRODCT SEQ SEQ
000008 *==== == ====== === ====== ====== ======
000009 MAY 12 400972 770 XXXXXA 000502 001456 SEEDS
000010 MAY 12 400972 781 XXXXXB 000502 004456 SEEDS
000011 MAY 12 400972 789 XXXXXC 000502 001233 SEEDS
000012 MAY 12 400972 791 XXXXXD 000506 000045 SEEDS
000013
000014 MAY 12 400977 416 XAXAXA 001002 000022 SEEDS
000015 MAY 12 400977 416 XAXAXB 701002 000001 SEEDS
000016 MAY 12 400977 421 XAXAXC 201002 034556 SEEDS
000017 MAY 12 400977 421 XAXAXD 501002 003422 SEEDS
000018 MAY 12 400977 425 XAXAXE 201002 000099 SEEDS
000019 MAY 12 400977 425 XAXAXF 501002 007566 SEEDS
000020 MAY 12 400977 425 XAXAXG 001002 196663 SEEDS
000021 MAY 12 400977 425 XAXAXH 701002 000301 SEEDS
000022 MAY 12 400977 426 XAXAXI 201003 000000 SEEDS
000023 MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS |
|
|
| Back to top |
|
|
ArunkumarGanesan
New User
Joined: 09 Jan 2020
Posts: 8
Location: USA
|
|
|
|
| Thank you Joerg. Worked as expected. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
Location: Bamberg, Germany
|
|
|
|
| ArunkumarGanesan wrote: |
| Thank you Joerg. Worked as expected. |
As it should. Thanks for the positive feedback.  |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
Frankly speaking, this way of resolving such a problem is a sort of workaround: processing all garbage/headers/etc. records along with real data lines, and the re-order resulting lines by initial record numbers. Some accidentally matching garbage/header records may produce unexpected results.
Correct way would be:
1) Omit all non-data records from both input files by using INCLUDE/OMIT parameters of JOINKEYS statements
2) Match/build/overlay required data-only lines as needed
3) In OUTFIL statement, re-generate clear new HEADER/TRAILER/SECTION records as needed.
It looks a bit longer to code, but logically it would be more correct way of processing this case. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
Location: Bamberg, Germany
|
|
|
|
From the presented data it should not be such a great overhead to incorporate your recommendations here.  |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1430
Location: Bamberg, Germany
|
|
|
|
| Code: |
//SYSIN DD *
JOINKEYS F1=F1,FIELDS=(1,34,A)
JOINKEYS F2=F2,FIELDS=(1,34,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,?,F2:35,6,F1:100,8)
SORT FIELDS=(88,8,ZD,A)
OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),
BUILD=(1,34,82,6,41,40))
OUTFIL FNAMES=(SORTOUT),
REMOVECC,
HEADER1=(50C'*',/,C'* UPDATE AS REQUIRED FOR EACH RUN.',/,C'*',/,
50C'*',/),
HEADER2=(C'*MNTH START NEXT',/,
C'*ID VL REGION ARA PRODCT SEQ SEQ',/,
C'*==== == ====== === ====== ====== ======'),
SECTIONS=(3,3,7,2,10,6,
TRAILER3=(X))
END
/*
//JNF1CNTL DD *
INCLUDE COND=((3,3,SS,EQ,
C'JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC'),
AND,(7,2,ZD,EQ,NUM),AND,(10,6,ZD,EQ,NUM))
INREC IFTHEN=(WHEN=INIT,OVERLAY=(100:SEQNUM,8,ZD))
END
/*
//JNF2CNTL DD *
INCLUDE COND=((3,3,SS,EQ,
C'JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC'),
AND,(7,2,ZD,EQ,NUM),AND,(10,6,ZD,EQ,NUM))
END
/* |
Output:
| Code: |
000001 **************************************************
000002 * UPDATE AS REQUIRED FOR EACH RUN.
000003 *
000004 **************************************************
000005
000006 *MNTH START NEXT
000007 *ID VL REGION ARA PRODCT SEQ SEQ
000008 *==== == ====== === ====== ====== ======
000009 MAY 12 400972 770 XXXXXA 000502 001456 SEEDS
000010 MAY 12 400972 781 XXXXXB 000502 004456 SEEDS
000011 MAY 12 400972 789 XXXXXC 000502 001233 SEEDS
000012 MAY 12 400972 791 XXXXXD 000506 000045 SEEDS
000013
000014 MAY 12 400977 416 XAXAXA 001002 000022 SEEDS
000015 MAY 12 400977 416 XAXAXB 701002 000001 SEEDS
000016 MAY 12 400977 421 XAXAXC 201002 034556 SEEDS
000017 MAY 12 400977 421 XAXAXD 501002 003422 SEEDS
000018 MAY 12 400977 425 XAXAXE 201002 000099 SEEDS
000019 MAY 12 400977 425 XAXAXF 501002 007566 SEEDS
000020 MAY 12 400977 425 XAXAXG 001002 196663 SEEDS
000021 MAY 12 400977 425 XAXAXH 701002 000301 SEEDS
000022 MAY 12 400977 426 XAXAXI 201003 000000 SEEDS
000023 MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS
|
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
Yes, this is what I meant 
Besides of this, after all non-data records are filtered out, it becomes possible to rely on pre-sorted order of input data. When using FIELDS=(1,34,A),SORTED it would become unnecessary neither to include SEQNUM, nor to re-sort the final result on this SEQNUM field.
This is irrelevant for small sample "toy projects", but becomes much more serious when huge datasets are to be joined. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
