|
View previous topic :: View next topic
|
| Author |
Message |
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Need to compare 2 fields in 2 files before joining them
Key positions :
Key1 : file1 : 7th col, 3 char to be compared with file2 : 7th col, 3 char
Key2 : file1 : 14th col, 5 char to be compared with file2 : 1st col, 5 char
File1 format
| Code: |
|014|CI|PL001| |002|
|
File2 format
| Code: |
PL001;014;NORTH COAST HOUSE
|
Output format
| Code: |
|014|CI|PL001|NORTH COAST HOUSE |002|
|
|
|
| Back to top |
|
 |
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
What is the RECFM and LRECL of each input file?
If the keys in file1 and file2 don't match, do you want to omit those records from the output file?
Can there be duplicate keys within input file1?
Can there be duplicate keys within input file2?
Please show an example with more than one record in each input file that covers all of the possible variations, and the expected output records. |
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Hi Frank
Lrecl for file1 is 53
Lrecl for file2 is 155
With the key combination of two fields, there can be duplicates in file1, but none in file2 |
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Frank..
Forgot to mention - there will definitely be records in file2 with the combination key for all recods in file1
File1
| Code: |
|620|KS|DC001| |002|HULLO|
AC210|021|IN|DC002| |002|HOLLA|
AD210|021|IN|DC004| |002|HOLLA|
|
File2:
| Code: |
DC001;620;COL.WHSE.BKY.STORG. ;1299 ALUM CREEK DR.
DC002;021;COLS.COOKIE WHSE STR;2880 WILLIAMS DR.
DC004;021;COLS. BAKERY STORES ;1100 PORT DR.
|
Output
| Code: |
AC210|620|KS|DC001|COLS.WHSE.BKY.STORG. |002|HULLO|
AC210|021|IN|DC002|COLS.COOKIE WHSE STR |002|HULLO|
AD210|021|IN|DC004|COLS.CAKE STORAGE |002|HOLLA|
|
|
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Sorry Frank..
the output file is supposed to be
| Code: |
|620|KS|DC001|COLS.WHSE.BKY.STORG. |002|HULLO|
AC210|021|IN|DC002|COLS.COOKIE WHSE STR |002|HULLO|
AD210|021|IN|DC004|COLS.CAKE STORAGE |002|HOLLA|
|
|
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
I believe your DC004 record should have COLS. BAKERY STORES.
At any rate, here's a DFSORT/ICETOOL job that will do what you asked for. The output records will be in key1/key2 order. If you want them in a different order, you can do another SORT.
| Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN2 DD DSN=... input file2 (FB/155)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
// DD DSN=... input file1 (FB/53)
//OUT DD DSN=... output file (FB/53)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
SPLICE FROM(CON) TO(OUT) ON(7,3,CH) ON(14,5,CH) -
WITHALL WITH(1,19) WITH(40,14)
/*
//CTL1CNTL DD *
INREC BUILD=(7:7,3,14:1,5,20:11,20,53:X)
/*
|
|
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Thanks frank,
Your suggestion is working fine. |
|
| Back to top |
|
|
kaleelahamed
New User
Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT
|
|
|
|
I used the above forum for my work...
And its working perfect....Thanks once again for the Author and Moderator...Thanks  |
|
| Back to top |
|
|
|
|
