Page 1 of 1

Extracting details records based on header record criteria

PostPosted: Sun Mar 21, 2010 10:38 pm
by pacha
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 ?

Re: Extracting details records based on header record criteria

PostPosted: Mon Mar 22, 2010 8:59 pm
by skolusu
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)       
//*

Re: Extracting details records based on header record criteria

PostPosted: Tue Mar 23, 2010 11:26 am
by pacha
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.

Re: Extracting details records based on header record criteria

PostPosted: Tue Mar 23, 2010 9:23 pm
by skolusu
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)
//*