IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Extracting records depending upon cond in another file
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Mon Jun 25, 2012 11:36 pm
 Reply with quote
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?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Tue Jun 26, 2012 12:18 am
 Reply with quote
Hello and welcome to the forum,

Suggest you consider creating symnames.

Create the symnames from the values in file one and use these to extract the records you want from file 2.

You could have a pre-process step to generate the symnames from the data in file 1.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 12:25 am
 Reply with quote
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

Appreciate if you could do that using code tags
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Jun 26, 2012 1:04 am
 Reply with quote
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.
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 10:49 am
 Reply with quote
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.

File 1
----+----+----+----+
11111100001101002
22222101002101201
33333101102101159
44444101520101645
22222101701101950
44444102045102245

File 2
----+----+----+
11111090020A1
11111100020A1
11111100100A2
11111100320B1
11111100550C1
11111101005D1
11111101105D2
12222101005D1
12223101005D1
22222101010A1
22222101130F1
22222101210B1
22222101320B1
22222101421B1
22222101721B1
22222101831G1
22222102021C1
23333102021C1
33333101021B1
33333101135V1
33333101235V1
44444101121C1
44444101221D1
44444101421R1
44444101529W1
44444101610D1
44444101620E1
44444101920E3
44444102020E3
44444102059B2
44444102110A2
44444102220A2
44444102320B2

Output will be the records from file2 "Exclusing" the ones highlighted in Red below.

Output
----+----+----+
11111090020A1
11111100020A1
11111100100A2
11111100320B1
11111100550C1
11111101005D1
11111101105D2
12222101005D1
12223101005D1
22222101010A1
22222101130F1
22222101210B1
22222101320B1
22222101421B1
22222101721B1
22222101831G1
22222102021C1
23333102021C1
33333101021B1
33333101135V1
33333101235V1
44444101121C1
44444101221D1
44444101421R1
44444101529W1
44444101610D1
44444101620E1
44444101920E3
44444102020E3
44444102059B2
44444102110A2
44444102220A2
44444102320B2
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 11:05 am
 Reply with quote
Does the records including the dates repeat

Have you worked on JOINKEYS?

All you need to do is Join two files for paired records

Reformat them ( You just need start and end date from file 1) while whole data from File 2

Have an include condition to fetch the records from F2 where time is between start and end time of FILE 1

Eliminate duplicates for whole record
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 11:26 am
 Reply with quote
@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
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 12:50 pm
 Reply with quote
I used the following SORTCARD

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   
    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?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 1:09 pm
 Reply with quote
Try this

Code:
SORT FIELDS=(13,13,CH,A)
SUM FIELDS=NONE
OUTREC FIELDS=(1:13,13)
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 1:20 pm
 Reply with quote
First attempt didnt work
Code:

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 icon_sad.gif
11111100020A1
22222101010A1
33333101135V1
44444101529W1
22222101721B1
44444102059B2
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 1:22 pm
 Reply with quote
Did you read my earlier post?
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 1:28 pm
 Reply with quote
Miss read it.. icon_sad.gif

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=(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
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 1:34 pm
 Reply with quote
Was this the expected output?
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK
PostPosted: Tue Jun 26, 2012 2:47 pm
 Reply with quote
Brother brother....

could you check this again:

Code:
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     
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Jun 26, 2012 3:02 pm
 Reply with quote
Good catch
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 3:05 pm
 Reply with quote
Extremely sorry. It was solved by the solution provided by Pandora-Box. I had incorrectly written the INCLUDE COND. Below card solved the issue icon_smile.gif

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)                                             
/*                                                               


Thanks for the support.
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India
PostPosted: Tue Jun 26, 2012 3:06 pm
 Reply with quote
@Naish: Yes, just got the same. Thanks for pointing icon_smile.gif
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Identify and write records containing... SYNCSORT 11
No new posts Filter records where condition is in ... SYNCSORT 6
No new posts To Populate Trailer cnt in the file w... SYNCSORT 1
No new posts Extract records for a specific condit... DFSORT/ICETOOL 25
No new posts Sort card to know that creation date ... DFSORT/ICETOOL 10
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us