MVSFORUMS.com :: View topic

through

[^/[Print]\]

MVSFORUMS.com -> Utilities

#1: Join two files Author: jajularamesh,

Posted: Thu Apr 19, 2007 11:42 pm
—
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

#2: Author: vkphani,

Posted: Fri Apr 20, 2007 12:17 am
—
Ramesh,

Pls post your sample input and expected output.

#3: Author: jajularamesh,

Posted: Fri Apr 20, 2007 1:07 am
—
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

#4: Author: kolusu, Location: San Jose

Posted: Fri Apr 20, 2007 4:50 am
—
jajularamesh,

Did you search the forums?

Kolusu

#5: Author: amargulies,

Posted: Fri Apr 20, 2007 11:22 am
—
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
/*

#6: Author: jajularamesh,

Posted: Sat Apr 21, 2007 6:39 am
—
This is not working in my shop

#7: Author: Frank Yaeger, Location: San Jose

Posted: Sat Apr 21, 2007 9:58 am
—
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)
/*

#8: Author: amargulies,

Posted: Mon Apr 23, 2007 9:00 am
—

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?

#9: JOINKEYS to join 3 files Author: rama krishna reddy, Location: Hyderabad

Posted: Thu Jul 17, 2008 7:35 am
—
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

#10: Author: Terry_Heinze, Location: Richfield, MN, USA

Posted: Thu Jul 17, 2008 11:22 am
—
What's the answer to Amargulies question?

#11: Author: amargulies,

Posted: Thu Jul 17, 2008 1:04 pm
—
Rama krishna reddy,

A new thread was already started with this EXACT same question...
http://www.mvsforums.com/helpboards/viewtopic.php?t=10003

MVSFORUMS.com -> Utilities

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1