Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Extracting records depending upon cond in another file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 32
Location: India

PostPosted: Mon Jun 25, 2012 11:36 pm    Post subject: Extracting records depending upon cond in another file
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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 26, 2012 12:18 am    Post subject:
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 12:25 am    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jun 26, 2012 1:04 am    Post subject: Reply to: Extracting records depending upon cond in another
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 10:49 am    Post subject:
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 11:05 am    Post subject:
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 11:26 am    Post subject:
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 12:50 pm    Post subject: Reply to: Extracting records depending upon cond in another
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 1:09 pm    Post subject:
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 1:20 pm    Post subject: Reply to: Extracting records depending upon cond in another
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 1:22 pm    Post subject:
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 1:28 pm    Post subject: Reply to: Extracting records depending upon cond in another
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 1:34 pm    Post subject:
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    Post subject:
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

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 3:02 pm    Post subject:
Reply with quote

Good catch
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 32
Location: India

PostPosted: Tue Jun 26, 2012 3:05 pm    Post subject: Reply to: Extracting records depending upon cond in another
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: 32
Location: India

PostPosted: Tue Jun 26, 2012 3:06 pm    Post subject: Reply to: Extracting records depending upon cond in another
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us