| View previous topic :: View next topic |
| Author |
Message |
jajularamesh
Beginner
Joined: 14 Apr 2006
Posts: 87
Topics: 33
|
 Posted: Thu Apr 19, 2007 11:42 pm Post subject: Join two files |
 |
|
i have a requirement to join two files.
The operation should be similar to left outer join can anyone please let me know how can this be done without writing a cobol program.
Suppose File1 has 4 fields and file2 has 4fileds
field4 of file1 can have duplicate values and field1 of file2 has no duplicate values.
So how can we combine these two files and the output file should contain seven field the data in the output file should be like the resultant of Leftouter join.
Can anyone let me know how to solve this problem.
Regards,
Ramesh |
|
| Back to top |
|
 |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Fri Apr 20, 2007 12:17 am Post subject: |
|
|
Ramesh,
Pls post your sample input and expected output. |
|
| Back to top |
|
|
jajularamesh
Beginner
Joined: 14 Apr 2006
Posts: 87
Topics: 33
|
| Posted: Fri Apr 20, 2007 1:07 am Post subject: |
|
|
Phani,
First File
| Code: |
Field1 Field2 Field3 field4
11 222 333 aaa
12 222 333 aaa
111 222 333 bbb
2 11 1 ccc
|
Second File
| Code: |
Field1 Field2 Field3 field4
aaa 22 333 aaa1
bbb 222 333 aaa
ddd 222 333 bbb
|
Out put should be | Code: |
second file fields
Field1 Field2 Field3 field4 Field2 Field3 field4
11 222 333 aaa 22 333 aaa1
12 222 333 aaa 22 333 aaa1
111 222 333 bbb 222 333 aaa
2 11 1 ccc
|
In the above given format i want the result
The last record has spaces in field2,3,4 as no match is found those values are nulls |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
amargulies
Beginner
Joined: 10 Jan 2007
Posts: 123
Topics: 0
|
| Posted: Fri Apr 20, 2007 11:22 am Post subject: |
|
|
jajularamesh,
The following SyncSort control statements will give you the desired output: | Code: | //STEP1 EXEC PGM=SORT
//SORTOUT DD SYSOUT=*
//SORTJNF1 DD *
11 222 333 AAA
12 222 333 AAA
111 222 333 BBB
2 11 1 CCC
//SORTJNF2 DD *
AAA 22 333 AAA1
BBB 222 333 AAA
DDD 222 333 BBB
//SORTOF01 DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(30,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,40,F2:10,30)
SORT FIELDS=COPY
/* |
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com |
|
| Back to top |
|
|
jajularamesh
Beginner
Joined: 14 Apr 2006
Posts: 87
Topics: 33
|
| Posted: Sat Apr 21, 2007 6:39 am Post subject: |
|
|
| This is not working in my shop |
|
| Back to top |
|
|
Frank Yaeger
Sort Forum Moderator
Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
|
| Posted: Sat Apr 21, 2007 9:58 am Post subject: |
|
|
Ramesh,
Here's a DFSORT/ICETOOL job that will do what you asked for:
| Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
11 222 333 aaa
12 222 333 aaa
111 222 333 bbb
2 11 1 ccc
/*
//IN2 DD *
aaa 22 333 aaa1
bbb 222 333 aaa
ddd 222 333 bbb
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(23,3,CH) KEEPNODUPS -
WITHALL WITH(1,22) WITH(50,1) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(23:1,27,50:C'BB')
/*
//CTL2CNTL DD *
INREC OVERLAY=(50:C'VV')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(50,1,CH,EQ,C'BB'),
BUILD=(1,49)
/*
|
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort |
|
| Back to top |
|
|
amargulies
Beginner
Joined: 10 Jan 2007
Posts: 123
Topics: 0
|
| Posted: Mon Apr 23, 2007 9:00 am Post subject: |
|
|
| Quote: |
This is not working in my shop
|
The JOIN application requires SyncSort for z/OS 1.2. What sort utility and release are you running? |
|
| Back to top |
|
|
rama krishna reddy
Beginner
Joined: 18 Sep 2006
Posts: 31
Topics: 13
Location: Hyderabad
|
| Posted: Thu Jul 17, 2008 7:35 am Post subject: JOINKEYS to join 3 files |
|
|
Hi ,
I need to join 3 files using syncsort ,is it possible to join 3 files using
JOIN KEYS of syncsort , if not is there any other option in sync sort where
i can join 3 files and i can write matching records into an output file
Please help me regarding the same
Thanks
Rama krishna reddy |
|
| Back to top |
|
|
Terry_Heinze
Supermod
Joined: 31 May 2004
Posts: 391
Topics: 4
Location: Richfield, MN, USA
|
| Posted: Thu Jul 17, 2008 11:22 am Post subject: |
|
|
What's the answer to Amargulies question?
_________________
....Terry |
|
| Back to top |
|
|
amargulies
Beginner
Joined: 10 Jan 2007
Posts: 123
Topics: 0
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
