Extracting details records based on header record criteria



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Extracting details records based on header record criteria

Postby pacha » Sun Mar 21, 2010 10:38 pm

One file consists of header records and detail records.
Key of the file is Emp no, Dept no and Record type.
Here record-type=0 means header. 1,2 etc indicates detail records. EMP-NO and DEPT-NO fields are common to both header and detail records. Emp-status field is only present in header records. I want to extract all the employee records where emp-status=confirmed. But it should extract the corresponding detail records also.

EMP-NO DEPT-NO RECORD-TYPE EMP-STATUS REMAINING DATA
X(4) X(2) 9(2) X(9) X(33)

A111 B1 0 TEMPORARY
A111 B1 1 ………………………………………
A111 B1 2 ………………………………………
B111 B1 0 CONFIRMED
B111 B1 1 ………………………………………
B111 B1 2 ………………………………………
C111 B1 0 TEMPORARY
C111 B1 1 ………………………………………
C111 B1 2 ………………………………………
C111 B1 3 ………………………………………
D111 B1 0 CONFIRMED
D111 B1 1 ………………………………………


Output should be
B111 B1 0 CONFIRMED
B111 B1 1 ………………………………………
B111 B1 2 ………………………………………
D111 B1 0 CONFIRMED
D111 B1 1 ………………………………………

Could someone please help me out ?
pacha
 
Posts: 10
Joined: Sun Mar 21, 2010 10:27 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extracting details records based on header record criteria

 

Re: Extracting details records based on header record criteria

Postby skolusu » Mon Mar 22, 2010 8:59 pm

pacha,

Use the following DFSORT JCL which will give you the desired results

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD DSN=your input FB 50 byte file,DISP=SHR         
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                           
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(7,2,ZD,EQ,0),PUSH=(51:9,9))
  OUTFIL INCLUDE=(51,9,CH,EQ,C'CONFIRMED'),BUILD=(1,50)       
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Extracting details records based on header record criteria

Postby pacha » Tue Mar 23, 2010 11:26 am

Skolusu,
Thanks for providing me the solution. But my installation is having April, 2006 DFSORT functions. GROUP and PUSH functions are not available in our version. Updation to the latest version will take time :x . Is it possible to achieve the same result using some other methods.
pacha
 
Posts: 10
Joined: Sun Mar 21, 2010 10:27 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Extracting details records based on header record criteria

Postby skolusu » Tue Mar 23, 2010 9:23 pm

pacha,

Use the following DFSORT/ICETOOL JCL

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*     
//DFSMSG   DD SYSOUT=*     
//IN       DD DSN=your input FB 50 byte file,DISP=SHR
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
  SPLICE FROM(IN) TO(OUT) ON(51,8,CH) WITH(1,50) WITHALL -         
  KEEPBASE USING(CTL1)                                             
//CTL1CNTL DD *                                                   
  SORT FIELDS=COPY                                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(51:SEQNUM,8,ZD)),               
  IFTHEN=(WHEN=(7,2,ZD,EQ,0),OVERLAY=(51:SEQNUM,8,ZD,9,9)),       
  IFTHEN=(WHEN=NONE,OVERLAY=(59:SEQNUM,8,ZD,                       
          51:51,8,ZD,SUB,59,8,ZD,M11,LENGTH=8))                   
  OUTFIL FNAMES=OUT,INCLUDE=(59,9,CH,EQ,C'CONFIRMED'),BUILD=(1,50)
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post