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
 

 

Control break and select 1st max timestamp set record in JCL

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

New User


Joined: 10 Jan 2008
Posts: 29
Location: Chennai, India

PostPosted: Mon Mar 25, 2013 2:48 pm    Post subject: Control break and select 1st max timestamp set record in JCL
Reply with quote

Hi,

I have the following input file in which i need to select the max timestamp records based on the EMP# and TASK ID. We need to use SORT, SYNCSORT and not icetool. Please see the example below.

Input File
-----------

Code:

EMP#         TASK ENTERED                TASK ID     TASK NAM  MANAGER
22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.919678  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982218  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982418  0000001122  DESG CHG  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R


Output File
------------

Code:

EMP#         TASK ENTERED                TASK ID     TASK NAM  MANAGER
22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R


Thanks in advance.
Back to top
View user's profile Send private message

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Mon Mar 25, 2013 3:04 pm    Post subject:
Reply with quote

Hello,
Quote:
select the max timestamp records based on the EMP# and TASK ID.

For the same EMP# and TASK ID there are multiple entries on the output, But you said you wanted only the maximum timestamp.

A little more explanation would certainly help.
Code:
22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
Back to top
View user's profile Send private message
Suceender Kumar

New User


Joined: 10 Jan 2008
Posts: 29
Location: Chennai, India

PostPosted: Mon Mar 25, 2013 3:12 pm    Post subject:
Reply with quote

Sorry. I need the max date recrods. So you can consider the first 10 byte of TASK ENTERED field.

Group By EMP#, TASK ID.
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Tue Mar 26, 2013 4:43 pm    Post subject: Reply to: Control break and select 1st max timestamp set rec
Reply with quote

Hi Kumar,

Try this code...

Code:

//STEP01 EXEC PGM=SORT                                               
//SYSPRINT DD SYSOUT=*                                               
//SYSOUT   DD SYSOUT=*                                               
//SORTOUT  DD DSN=&&TEMP1,DISP=(,PASS,DELETE),SPACE=(CYL,(1,1),RLSE) 
//SORTXSUM DD DSN=&&TEMP2,DISP=(,PASS,DELETE),SPACE=(CYL,(1,1),RLSE) 
//SORTIN   DD *                                                       
22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.919678  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982218  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982418  0000001122  DESG CHG  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R
//SYSIN   DD *                     
  SORT FIELDS=(1,8,CH,A,42,10,CH,A)
  SUM FIELDS=NONE,XSUM             
//*
//STEP02 EXEC PGM=SORT                                               
//SYSPRINT DD SYSOUT=*                                               
//SYSOUT   DD SYSOUT=*                                               
//SORTJNF1 DD DSN=&&TEMP1,DISP=SHR                                   
//SORTJNF2 DD DSN=&&TEMP2,DISP=SHR                                   
//OUTPUT1  DD DSN=&&TEMP3,DISP=(,PASS,DELETE),SPACE=(CYL,(1,1),RLSE)
//SYSIN   DD *                                                       
  JOINKEYS FILES=F1,FIELDS=(1,8,A,42,10,A,14,10,A)                   
  JOINKEYS FILES=F2,FIELDS=(1,8,A,42,10,A,14,10,A)                   
  REFORMAT FIELDS=(F1:1,80,F2:1,80)                                 
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(151:81,80,81:1,8,14,26,42,10,     
                                    81:SEQNUM,2,ZD,RESTART=(81,44))),
        IFTHEN=(WHEN=(81,2,ZD,GE,+2),OVERLAY=(1:151,80,151:80X))
           OUTFIL FNAMES=OUTPUT1,OUTREC=(1,80,/,151,80)
//*                                           
//STEP03 EXEC PGM=SORT                         
//SYSPRINT DD SYSOUT=*                         
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD DSN=&&TEMP3,DISP=SHR             
//SORTOUT DD SYSOUT=*                         
//SYSIN   DD *                                 
  SORT FIELDS=COPY                             
  OMIT COND=(1,1,CH,EQ,C' ')


Thanks
-3nadh
Back to top
View user's profile Send private message
Suceender Kumar

New User


Joined: 10 Jan 2008
Posts: 29
Location: Chennai, India

PostPosted: Mon Apr 01, 2013 3:46 pm    Post subject: Reply to: Control break and select 1st max timestamp set rec
Reply with quote

Thanks a lot bodatrinadh! It worked! Sorry for my late reply.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Mon Apr 01, 2013 5:30 pm    Post subject: Reply to: Control break and select 1st max timestamp set rec
Reply with quote

You might like to try this:

Code:
//STEP01 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN   DD *
  OPTION COPY
* OPTION EQUALS
* SORT FIELDS=(1,8,CH,A,42,10,CH,A,14,10,CH,D)
  OUTREC IFTHEN=(WHEN=INIT,
                   OVERLAY=(81:
                             001,008,
                             042,010,
                             SEQNUM,3,ZD,
                              RESTART=(81,18))),
         IFTHEN=(WHEN=GROUP,BEGIN=(99,3,CH,EQ,C'001'),
                  PUSH=(103:14,10))
  OUTFIL INCLUDE=(14,10,CH,EQ,103,10,CH),BUILD=(1,80)
//*
//SORTIN   DD *
22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.919678  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982218  0000001122  DESG CHG  CAROL R
22215687     2013-02-14-23.32.52.982418  0000001122  DESG CHG  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-11-23.32.52.982418  0000001123  BONUS AD  CAROL R


This gives:
Code:

22215687     2013-02-16-06.30.16.071719  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.631131  0000001122  DESG CHG  CAROL R
22215687     2013-02-16-06.30.16.724981  0000001122  DESG CHG  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R
22215687     2013-02-12-23.32.52.982418  0000001123  BONUS AD  CAROL R


It uses RESTART (as SyncSort does not have KEYBEGIN) to identify the first record of a "group" (emp and task) then identifies the first record to form a GROUP, putting the date from the first record to all members of the GROUP.

Then OUTFIL selects the records whose date is the same as the date of the first record.

If your data is not already in sequence, uncomment the SORT statement and the OPTION EQUALS and delete the OPTION COPY. If it is in sequence, delete the commented SORT statement and OPTION EQUALS, and you can also change OUTREC to INREC, though not vital.
Back to top
View user's profile Send private message
Suceender Kumar

New User


Joined: 10 Jan 2008
Posts: 29
Location: Chennai, India

PostPosted: Wed Apr 03, 2013 11:05 am    Post subject: Reply to: Control break and select 1st max timestamp set rec
Reply with quote

Hello Bill,

It worked as expected! Thanks for the detailed explanation.
Thanks Everyone!
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm


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