Lookup in 3 files



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

Lookup in 3 files

Postby selva1849 » Thu Jul 07, 2011 11:17 pm

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?
selva1849
 
Posts: 3
Joined: Sat Jun 25, 2011 11:57 am
Has thanked: 0 time
Been thanked: 0 time

Re: Lookup in 3 files

Postby skolusu » Fri Jul 08, 2011 12:17 am

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 ?
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: Lookup in 3 files

Postby Frank Yaeger » Fri Jul 08, 2011 1:28 am

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         
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Lookup in 3 files

Postby selva1849 » Sat Jul 09, 2011 1:41 pm

Frank,

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

Thanks,
Selva1849
selva1849
 
Posts: 3
Joined: Sat Jun 25, 2011 11:57 am
Has thanked: 0 time
Been thanked: 0 time

Re: Lookup in 3 files

Postby selva1849 » Mon Jul 11, 2011 8:00 am

Frank,

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

Thanks,
Selva1849
selva1849
 
Posts: 3
Joined: Sat Jun 25, 2011 11:57 am
Has thanked: 0 time
Been thanked: 0 time

Re: Lookup in 3 files

Postby dick scherrer » Mon Jul 11, 2011 9:25 am

Hello,

You need to post the complete diagnostic information generated by the run including all message ids.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Lookup in 3 files

Postby Frank Yaeger » Mon Jul 11, 2011 10:36 pm

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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post