Page 1 of 1

Lookup in 3 files

PostPosted: Thu Jul 07, 2011 11:17 pm
by selva1849
I have 3 flat files.
File A
Field1 Field 2
12345 8778878
12999 787887
23456 778675
67334 785787856
35332 7576767

File B
Field1 Field 2
12345 7846744
23456 467467


File C
Field1 Field2
12345 5634564
67334 5456456
35332 5645644


I want to compare these three files. The first field in each file is the key. My requirement is to take Field1 value(12345) from File A and look for this value in File B and File C. If the value is found then corresponding field2 value from File B(7846744) and File C(5634564) shd be taken and wriiten in output file like this

Output

12345 8778878 7846744 5634564


If the lookup value is not present in File B or File C or both then the output file shd have value 0 in those fields like this

Output
12999 787887 0 0
23456 778675 467467 0

Is this possible through DFSORT?

Re: Lookup in 3 files

PostPosted: Fri Jul 08, 2011 12:17 am
by skolusu
selva1849,

What is the LRECL and RECFM of all the 3 files?
What is the position, length and format of the key to be matched in all files
What is the position, length and format of the field to be merged ?

Re: Lookup in 3 files

PostPosted: Fri Jul 08, 2011 1:28 am
by Frank Yaeger
selva1849,

You can use a DFSORT/ICETOOL job like the following to do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, your key is in positions 1-5 and your data field is in position 7-15. Adjust as necessary.

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
12345 8778878
12999 787887
23456 778675
67334 785787856
35332 7576767
99999 123456789
//IN2 DD *
12345 7846744
23456 467467
99999 123456789
//IN3 DD *
12345 5634564
67334 5456456
35332 5645644
99999 123456789
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY JKFROM TO(T1) USING(CTL1)
COPY JKFROM TO(OUT) USING(CTL2)
//CTL1CNTL DD *
  JOINKEYS F1=IN1,FIELDS=(1,5,A)
  JOINKEYS F2=IN2,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,15,F2:6,10)
  OPTION COPY
  OUTREC OVERLAY=(80:X)
//CTL2CNTL DD *
  JOINKEYS F1=T1,FIELDS=(1,5,A),SORTED
  JOINKEYS F2=IN3,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,25,F2:6,10)
  OPTION COPY
  OUTREC IFOUTLEN=80,
    IFTHEN=(WHEN=(17,9,CH,EQ,C' '),OVERLAY=(17:C'0'),HIT=NEXT),
    IFTHEN=(WHEN=(27,9,CH,EQ,C' '),OVERLAY=(27:C'0'))
/*


OUT would have:

12345 8778878   7846744   5634564           
12999 787887    0         0                 
23456 778675    467467    0                 
35332 7576767   0         5645644           
67334 785787856 0         5456456           
99999 123456789 123456789 123456789         

Re: Lookup in 3 files

PostPosted: Sat Jul 09, 2011 1:41 pm
by selva1849
Frank,

Thank you! I will try this once I reach my office.

Thanks,
Selva1849

Re: Lookup in 3 files

PostPosted: Mon Jul 11, 2011 8:00 am
by selva1849
Frank,

I used the above code but getting SPANINC=16 error. Can you please help me in solving this?

Thanks,
Selva1849

Re: Lookup in 3 files

PostPosted: Mon Jul 11, 2011 9:25 am
by dick scherrer
Hello,

You need to post the complete diagnostic information generated by the run including all message ids.

Re: Lookup in 3 files

PostPosted: Mon Jul 11, 2011 10:36 pm
by Frank Yaeger
I used the above code but getting SPANINC=16 error. Can you please help me in solving this?


SPANINC=16 is a keyword which applies to VBS records, not an error, so I don't know what you mean.

Do your files have RECFM=VBS? I assumed they had RECFM=FB. I guess you need to answer Kolusu's questions about attributes, etc.

Also, show the complete JES log from your run.