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
 

 

Conditional selection of records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Sat Nov 03, 2007 3:20 am    Post subject: Conditional selection of records
Reply with quote

I need to select records from an input file conditionally. The file is FB with length 80.

The file has 2 fields (represented by A,B)followed by a date field, with duplicates.
A1 B1 20071102
A1 B1 20071102
A1 B1 20071101
A1 B1 20071015 - not max date for A1 B1 and before 10 days - reject.
A2 B2 20071015
A2 B2 20071015
A2 B2 20071014 - not max date for A2 B2 and before 10 days - reject.
A3 B3 20071010
A3 B3 20071010
A4 B4 20071101

I need to get all records with identical first two fields for the maximum date value and all records where date is in within the last 10 days.

Output
A1 B1 20071102
A1 B1 20071102
A1 B1 20071101
A2 B2 20071015
A2 B2 20071015
A3 B3 20071010
A3 B3 20071010
A4 B4 20071101

Please let me know if this can be done by sort?

Thanks,
Aneesh.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Nov 03, 2007 3:40 am    Post subject:
Reply with quote

Code:

A2 B2 20071015
A2 B2 20071015
A2 B2 20071014 - not max date for A2 B2 and before 10 days - reject.


20071014 is within 10 days of 20071015 so why would you reject it?

DFSORT/ICETOOL can do conditional selection of records, but it can't do this kind of date arithmetic on dates in records (yyyymmdd - 10 days).
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Sat Nov 03, 2007 4:17 am    Post subject: Reply to: Conditional selection of records
Reply with quote

Hi Frank,

The maximum date is the current date for which the process is running. Sorry for the confusion.

Thanks,
Aneesh.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Nov 03, 2007 5:24 am    Post subject:
Reply with quote

Just to be clear:

For each A/B field:

You want all records with the maximum date in the record.
You want any other records that are within 10 days of the current date.

To make sure I understand, for the following input, what would you expect as the output (with today's date of 20071102):

A1 B1 20071102
A1 B1 20071102
A1 B1 20071102
A1 B1 20071101
A1 B1 20071101
A1 B1 20071020
A1 B1 20071021
A1 B1 20071022
A1 B1 20071023
A1 B1 20071024
A1 B1 20071025
A1 B1 20071015
A1 B1 20071015
A2 B2 20071015
A2 B2 20071015
A2 B2 20071014
A2 B2 20071013
A2 B2 20071013
A3 B3 20071010
A3 B3 20071010
A3 B3 20071009
A4 B4 20071101
A5 B1 20071031
A5 B1 20071031
A5 B1 20071030
A5 B1 20071030
A5 B1 20071015
A5 B1 20071015
A6 B2 20071016

Also, what is the actual starting position, length and format of each of the three fields?
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Sat Nov 03, 2007 6:22 am    Post subject: Reply to: Conditional selection of records
Reply with quote

Hi Frank,

What you have mentioned is the objective.

The output should be
A1 B1 20071102
A1 B1 20071102
A1 B1 20071102
A1 B1 20071101
A1 B1 20071101
A1 B1 20071024
A1 B1 20071025
A2 B2 20071015
A2 B2 20071015
A3 B3 20071010
A3 B3 20071010
A4 B4 20071101
A5 B1 20071031
A5 B1 20071031
A5 B1 20071030
A5 B1 20071030
A6 B2 20071016

The input if FB 80 with A from 1-13(13), B from 14-17(4) and date from 50-57(8).

Thanks,
Aneesh.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sat Nov 03, 2007 11:39 am    Post subject:
Reply with quote

Aneesh
This is the ICETOOL solution i could think with 3 passes, but still i feel some where i could reduce one pass....
Code:
//*******************************************************               
//STEP001  EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
A1 B1 20071103                                                         
A1 B1 20071102                                                         
A1 B1 20071101                                                         
A1 B1 20071015                                                         
A2 B2 20071015                                                         
A2 B2 20071015                                                         
A2 B2 20071014                                                         
A3 B3 20071010                                                         
A3 B3 20071010                                                         
A4 B4 20071101                                                         
/*                                                                     
//TMP1     DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//TMP2     DD DSN=&&TEMP2,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//OUT1     DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 SORT FROM(IN1)  TO(TMP1) USING(CP01)                                   
 SORT FROM(TMP1)  TO(TMP2) USING(CP02)                                 
 SPLICE FROM(TMP2) TO(OUT1) ON(1,5,CH) WITH(7,8) -           
      WITHALL   KEEPNODUPS USING(CP03)                                 
/*                                                                     
//CP01CNTL DD   *                                                       
  INREC OVERLAY=(50:C'11')                                             
  SORT FIELDS=(1,5,CH,A)                                               
  OUTFIL REMOVECC,SECTIONS=(1,5,                                       
         TRAILER3=(1,5,20:MAX=(7,8,ZD),50:C'22'))                       
/*                                                                     
//CP02CNTL DD   *                                                       
  SORT FIELDS=(50,2,CH,D)                                               
/*                                                                     
//CP03CNTL DD   *                                                       
 OUTFIL INCLUDE=((7,8,ZD,EQ,20,8,ZD),|,7,8,CH,GE,DATE1-10),BUILD=(1,15)
/*


OUT1 contains:
Code:
A1 B1 20071103
A1 B1 20071102
A1 B1 20071101
A2 B2 20071015
A2 B2 20071015
A3 B3 20071010
A3 B3 20071010
A4 B4 20071101
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sat Nov 03, 2007 1:22 pm    Post subject:
Reply with quote

Yes here is the two pass approach icon_cool.gif
Code:
//*******************************************************               
//STEP001  EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD *                                                         
A1 B1 20071103                                                         
A1 B1 20071102                                                         
A1 B1 20071101                                                         
A1 B1 20071015                                                         
A2 B2 20071015                                                         
A2 B2 20071015                                                         
A2 B2 20071014                                                         
A3 B3 20071010                                                         
A3 B3 20071010                                                         
A4 B4 20071101                                                         
/*                                                                     
//TMP1     DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA 
//OUT1     DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 SORT FROM(IN1)  TO(TMP1) USING(CP01)                                   
 SPLICE FROM(TMP1) TO(OUT1) ON(1,5,CH) WITH(7,8) WITH(51,1)-           
      WITHALL   KEEPNODUPS USING(CP02)                                 
/*                                                                     
//CP01CNTL DD   *                                                       
  SORT FIELDS=(1,5,CH,A,7,8,ZD,D)                                       
  OUTREC OVERLAY=(60:SEQNUM,8,ZD,RESTART=(1,5))                         
  OUTFIL IFTHEN=(WHEN=(60,8,ZD,EQ,+1),                                 
                      BUILD=(1,5,20:7,8,/,1,27)),IFOUTLEN=27           
/*                                                                     
//CP02CNTL DD   *                                                       
 OUTFIL INCLUDE=((7,8,ZD,EQ,20,8,ZD),|,7,8,CH,GE,DATE1-10),BUILD=(1,15)
/*                                                                     


OUT1:
Code:
A1 B1 20071103
A1 B1 20071102
A1 B1 20071101
A2 B2 20071015
A2 B2 20071015
A3 B3 20071010
A3 B3 20071010
A4 B4 20071101
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Sun Nov 04, 2007 10:25 pm    Post subject: Reply to: Conditional selection of records
Reply with quote

Hi krisprems,

Thanks a lot icon_biggrin.gif . Both approaches were neat and cool. Thanks again.

Thanks,
Aneesh.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Nov 05, 2007 8:41 am    Post subject:
Reply with quote

icon_biggrin.gif
glad that i could help you
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


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