Hi, I want to extract records from file2, depending upon the conditions set in file1.
I'm using Syncsort for this.
File layout is as below:
File1
Field name : start pos, length, format, comment
Acct No: 1,5,CH
Start time: 6,6,ZD, time in HHMMSS
End time:12, 6,ZD, time in HHMMSS
File2
Acct No: 1,5,CH
Time: 6,6,ZD, time in HHMMSS
Code: 12,2,CH
File2 will have multiple records. My aim is to fetch only those accts from file2 such that those records should lie between start and end time mentioned in file1 for that particular acct. Is this possible using Syncsort or Cobol is the only solution?
Do you to fetch those accounts from file 2 where the matching accounts start time and end time from file1 is lying between sfart time and end time in file2?
Also could you post the sample records by simulating the data from both input files and the output you expect
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Do you have multiple records on file 1?
Might there be multiple records per account on file 1?
Follow Pandora-box's suggestion, making sure you cover all eventualities of your data. It is no fun to work out a solution and then find that the full information means it was a waste of time.
Thanks for the greetings 'dick scherrer'. Will try to find out more on 'Symnames' as I haven't used it before.
Pandora-Box & Bill Woodger, below is sample file for your reference. Please note, file1 has the 'Start and End Time' range while file2 just has the time.
Yes, file1 will also have duplicate acct numbers but the time range will be different.
@Pandora-Box: There are repeats in both the files, but the repeated records in file1 will have different time range and those ranges will NOT overlap with other. They will be mutually exclusive records.
Yes, I have used JOINKEYS, so you saying that I do a JOINKEY on the acct numbers -> the output will be start and end time of file1 and whole of file2 data > then using this o/p I create another file with INCLUDE cond as per my requirement. Will try that out and let you know. Thanks
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:6,6, F1 START TIME
12,6, F1 END TIME
F2:1,13) F2 TIME
INCLUDE COND=(18,6,ZD,GE,1,6,ZD,AND, F2 TIME GE F1 START TIME
18,6,ZD,LE,7,12,ZD) F2 TIME LE F1 END TIME
OUTFIL FNAMES=SORTOUT,
OUTREC=(1:13,13)
SORT FIELDS=(1,13,CH,A) SORT COMPLETE F2 RECORD
SUM FIELDS=NONE REMOVE DUPLICATE
/*
But the o/p wasn't as expected. Shown below
11111100020A1
22222101010A1
33333101135V1
44444101529W1
22222101721B1
44444102059B2
I tried couple of other trial and run method but wasn't successful. Can you pin point the mistake?
SYSIN :
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:6,6, F1 START TIME
12,6, F1 END TIME
F2:1,13) F2 TIME
INCLUDE COND=(18,6,ZD,GE,1,6,ZD,AND, F2 TIME GE F1 START TIME
18,6,ZD,LE,7,12,ZD) F2 TIME LE F1 END TIME
OUTFIL FNAMES=SORTOUT,
SORT FIELDS=(1,13,CH,A) SORT COMPLETE F2 RECORD
*
SUM FIELDS=NONE REMOVE DUPLICATE
OUTREC=(1:13,13)
*
WER268A OUTFIL STATEMENT : SYNTAX ERROR
WER268A OUTREC STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
2nd attempt
Code:
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:6,6, F1 START TIME
12,6, F1 END TIME
F2:1,13) F2 TIME
INCLUDE COND=(18,6,ZD,GE,1,6,ZD,AND, F2 TIME GE F1 START TIME
18,6,ZD,LE,7,12,ZD) F2 TIME LE F1 END TIME
SORT FIELDS=(1,13,CH,A) SORT COMPLETE F2 RECORD
SUM FIELDS=NONE REMOVE DUPLICATE
OUTFIL FNAMES=SORTOUT,
OUTREC=(1:13,13)
/*
Output still remain the same
11111100020A1
22222101010A1
33333101135V1
44444101529W1
22222101721B1
44444102059B2
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:6,6, F1 START TIME
12,6, F1 END TIME
F2:1,13) F2 TIME
INCLUDE COND=(18,6,ZD,GE,1,6,ZD,AND, F2 TIME GE F1 START TIME
18,6,ZD,LE,7,12,ZD) F2 TIME LE F1 END TIME
SORT FIELDS=(13,13,CH,A) SORT COMPLETE F2 RECORD
SUM FIELDS=NONE REMOVE DUPLICATE
OUTFIL FNAMES=SORTOUT,
OUTREC=(1:13,13)
/*
This time some extra records in o/p
11111100020A1
11111100100A2
11111100320B1
11111100550C1
11111101005D1
11111101105D2
22222101010A1
22222101130F1
22222101210B1
22222101320B1
22222101421B1
22222101721B1
22222101831G1
22222102021C1
33333101135V1
33333101235V1
44444101529W1
44444101610D1
44444101620E1
44444101920E3
44444102020E3
44444102059B2
44444102110A2
44444102220A2
44444102320B2
Extremely sorry. It was solved by the solution provided by Pandora-Box. I had incorrectly written the INCLUDE COND. Below card solved the issue
Code:
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:6,6, F1 START TIME
12,6, F1 END TIME
F2:1,13) F2 TIME
INCLUDE COND=(18,6,ZD,GE,1,6,ZD,AND, F2 TIME GE F1 START TIME
18,6,ZD,LE,7,6,ZD) F2 TIME LE F1 END TIME
SORT FIELDS=(13,13,CH,A) SORT COMPLETE F2 RECORD
SUM FIELDS=NONE REMOVE DUPLICATE
OUTFIL FNAMES=SORTOUT,
OUTREC=(1:13,13)
/*
.jpg)
