|
View previous topic :: View next topic
|
| Author |
Message |
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
Can someone please help on the below sort requirement
I have an requirement to compare current file with last run's file and extract only adds and modifications.
Problem is i should compare only header lines (dont compare detail lines) but my file has headers and Details.
For the extracted Header , I need all the details linked to that header from the current file irrespective of changes/adds.
Below is the sample file.
In Header, PO number starts at 5th byte ( lenght of 16 bytes)
PO status starts at 22nd byte (length of 3 bytes )
In Detail, PO number starts at 15th byte ( lenght of 16 bytes)
PO status starts at 56nd byte (length of 3 bytes )
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----
HDR|SS015000000180 |UPD|SST | |0000004.000|0000000.000|
DTL|006769407|SS015000000180 |0000001|0000001|0000001|UPD|
DTL|006798646|SS015000000180 |0000001|0000001|0000001|UPD|
DTL|006769399|SS015000000180 |0000001|0000001|0000001|UPD|
DTL|006769406|SS015000000180 |0000001|0000001|0000001|UPD|
HDR|SS015000000181 |UPD|SST | |0000000.000|0000002.000|
DTL|006710709|SS015000000181 |0000001|0000001|0000001|UPD|
DTL|006715969|SS015000000181 |0000001|0000001|0000001|UPD|
|
Below is the sort which will work for Headers but it will remove the details.
| Code: |
//SORTJOIN EXEC PGM=SORT,
// COND=(0,NE)
//SORTJNF1 DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS,
// DISP=SHR
//SORTJNF2 DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.BKUP(+0),
// DISP=SHR
//SORTOUT DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.NEW,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(00460,(100,10),RLSE),
// AVGREC=K,
// RECFM=FB,
// LRECL=00460
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=SORTJNF1,FIELDS=(1,650,A)
JOINKEYS F2=SORTJNF2,FIELDS=(1,650,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,650)
SORT FIELDS=COPY
/* |
I Guess below solution will work but is there any optimised way to reduce number of steps
1) Split the Headers and Detail records to backup files. ( MOFDSS.HDR.BKUP(+1) and MOFDSS.DTL.BKUP(+1) )
2) Compare Current MOFDSS.HDR.BKUP(+1) and Previous MOFDSS.HDR.BKUP(0) Header files to extract only New and modified Headers. (MOFDSS.HDR.NEW)
3) Merge files MOFDSS.HDR.NEW and matching details from MOFDSS.DTL.BKUP(+1) based on PO number and PO status to create MOFDSS.FINAL.EXT |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
Please use the code tags when presenting code and data. Fixed for you this time.
For the input you show, what is the expected output? |
|
| Back to top |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
Sorry Nic, Since I am using this for the first time, I was not aware how to add the code tags. Now I have modified my entire query and added the required output file.
I have an requirement to compare current file with last run's file and extract only adds and modifications.
Problem is i should compare only headers but my file has headers and Details.
For the extracted Header , I need all the details linked to that header from the current file irrespective of changes/adds.
Below is the sample file.
In Header, PO number starts at 5th byte ( lenght of 16 bytes)
PO status starts at 22nd byte (length of 3 bytes )
In Detail, PO number starts at 15th byte ( lenght of 16 bytes)
Previous File:
WE have only two PO's SS048000003114 and SS048000003115
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114 |UPD|SST | |2222222.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
|
Current file:
We have two PO's from last run SS048000003114 and SS048000003115 and a new PO is added SS048000003116
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114 |UPD|SST | |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
HDR|SS048000003116 |UPD|SST | |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD| |
OUTPUT file:
First PO# SS048000003114
In the previous file for the PO# SS048000003114 chill order quantity is 2222222.000
in the current file for the PO# SS048000003114 chill order quantity is 1111111.000 <-- write total set of header and detail into output file
Second PO# SS048000003115
There are no changes in the header in both the files, So skip the total set
Third PO# SS048000003116
This is newly added to the current file not exits in pervious file. <-- Write total set into output file
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114 |UPD|SST | |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
HDR|SS048000003116 |UPD|SST | |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
|
Below is the sort which will work for Headers but it will remove the details.
| Code: |
//SORTJOIN EXEC PGM=SORT,
// COND=(0,NE)
//SORTJNF1 DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS,
// DISP=SHR
//SORTJNF2 DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.BKUP(+0),
// DISP=SHR
//SORTOUT DD DSN=D&DIV1.MO.&PROD.N00&DIV2..$$.MOFDSS.NEW,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(00460,(100,10),RLSE),
// AVGREC=K,
// RECFM=FB,
// LRECL=00460
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=SORTJNF1,FIELDS=(1,650,A)
JOINKEYS F2=SORTJNF2,FIELDS=(1,650,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,650)
SORT FIELDS=COPY
/*
|
I Guess below solution will work but is there any optimised way to reduce number of steps
1) Split the Headers and Detail records to backup files. ( MOFDSS.HDR.BKUP(+1) and MOFDSS.DTL.BKUP(+1) )
2) Compare Current MOFDSS.HDR.BKUP(+1) and Previous MOFDSS.HDR.BKUP(0) Header files to extract only New and modified Headers. (MOFDSS.HDR.NEW)
3) Merge files MOFDSS.HDR.NEW and matching details from MOFDSS.DTL.BKUP(+1) based on PO number and PO status to create MOFDSS.FINAL.EXT[/code]
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
Your approach is completely wrong in general.
Not a minor understanding of JOIN operation.
This is a request to others, to do your job for you... |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
Not the answer you are seeking but...
in the time I spent thinking about this (not long) I could have written a small rexx program to do what you want. It is, basically, a 2-file match. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
| VINAY PALLELA wrote: |
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114 |UPD|SST | |2222222.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
|
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
HDR|SS048000003114 |UPD|SST | |1111111.000|0000000.000|0000000.000|0
DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
HDR|SS048000003116 |UPD|SST | |0000000.000|0000123.000|0000000.000|0
DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD| |
|
You need to consider 4 categories:
1) Newly inserted groups
2) Deleted old groups
3) Matching groups with changed data
4) Matching groups with unchanged data (plan to ignore them)
You may be interested in the first 3 of them.
Stage 1: Create matching file
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
3 HDR|SS048000003114 |
4 HDR|SS048000003115 |
1 HDR|SS048000003116 |
|
(Type 2 is not present in your example)
Stage 2: Indicate Group IDs in both inputs:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SS048000003114 HDR|SS048000003114 |UPD|SST | |2222222.000|0000000.000|0000000.000|0
SS048000003114 DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
SS048000003114 DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
SS048000003114 DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
SS048000003115 HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
SS048000003115 DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003115 DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003115 DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
|
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SS048000003114 HDR|SS048000003114 |UPD|SST | |1111111.000|0000000.000|0000000.000|0
SS048000003114 DTL|005200815|SS048000003114 |0000002|0000000|0000002|UPD|
SS048000003114 DTL|005258615|SS048000003114 |0000001|0000000|0000001|UPD|
SS048000003114 DTL|005198615|SS048000003114 |0000002|0000000|0000002|UPD|
SS048000003115 HDR|SS048000003115 |UPD|SST | |0000000.000|0000027.000|0000000.000|0
SS048000003115 DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003115 DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003115 DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003116 HDR|SS048000003116 |UPD|SST | |0000000.000|0000123.000|0000000.000|0
SS048000003116 DTL|009287835|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003116 DTL|008717424|SS048000003115 |0000001|0000001|0000001|UPD|
SS048000003116 DTL|009298821|SS048000003115 |0000001|0000001|0000001|UPD| |
Stage 3: Join the "Matching File" with each of "grouped" inputs, keeping in mind the '1'/'2'/'3'/'4' indicators set in the first of them.
Use either several JCL steps with SYNCSORT, or single JCL step with SYNCTOOL. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
Not a clean way but get you what you want.
| Code: |
//STEP01 EXEC PGM=SORT
//SORTLIST DD SYSOUT=*
//SORTJNF1 DD *
HDR|SS048000003114 |UPD|SST | |1111111.000
DTL1
DTL2
DTL3
HDR|SS048000003115 |UPD|SST | |2222222.000
DTL1
DTL2
DTL3
DTL4
//SORTJNF2 DD *
HDR|SS048000003114 |UPD|SST | |1111112.000
DTL1
DTL2
DTL3
HDR|SS048000003115 |UPD|SST | |2222222.000
DTL1
DTL2
DTL3
DTL4
HDR|SS048000003116 |UPD|SST | |3333333.000
DTL1
DTL2
DTL3
DTL4
//SORTOUT DD DISP=(NEW,PASS),
// UNIT=SYSDA,SPACE=(TRK,(10,10),RLSE),
// DSN=&&MASTER
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(1,3,CH,EQ,C'HDR')
JOINKEYS F1=SORTJNF1,FIELDS=(1,80,A)
JOINKEYS F2=SORTJNF2,FIELDS=(1,80,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F2:1,80,90,8)
OUTFIL BUILD=(1,88)
//*
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(90:ID=8))
//STEP02 EXEC PGM=SORT,REGION=500M
//SORTLIST DD SYSOUT=*
//SORTJNF1 DD DSN=&&MASTER,DISP=OLD
//SORTJNF2 DD *
HDR|SS048000003114 |UPD|SST | |1111112.000
DTL1
DTL2
DTL3
HDR|SS048000003115 |UPD|SST | |2222222.000
DTL1
DTL2
DTL3
DTL4
HDR|SS048000003116 |UPD|SST | |3333333.000
DTL1
DTL2
DTL3
DTL4
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS F1=SORTJNF1,FIELDS=(81,8,A)
JOINKEYS F2=SORTJNF2,FIELDS=(91,8,A)
REFORMAT FIELDS=(F2:1,80)
OUTFIL BUILD=(1,80)
//*
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(91:ID=8))
|
Output
| Code: |
HDR|SS048000003114 |UPD|SST | |1111112.000
DTL1
DTL2
DTL3
HDR|SS048000003116 |UPD|SST | |3333333.000
DTL1
DTL2
DTL3
DTL4 |
|
|
| Back to top |
|
|
VINAY PALLELA
New User
Joined: 28 Jan 2018
Posts: 24
Location: India
|
|
|
|
Thank you very much! Rohit Umarjikar
I tried your sort steps and its working as expected.
Thank you all for taking time to give the inputs. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
Only the COPY-AND-PASTE type of answers is working in this forum, despite of the name Expert Forum...
It's a pity, software development has changed to a sort of computer games - click the button Start, and get the result. Not even any attempt to think about the approach, or algorithm. I suspect most of TS did not even hear about words like "algorithm"  |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
