sivatechdrive
Active User
Joined: 17 Oct 2004 Posts: 191 Location: hyderabad
|
|
|
|
Hi All,
I am not much familier with SPLICE. Could someone help me
This have a basic requirement in SPLICE tool, my File1 contains duplicat keys (i.e agent no is the key AG000056481 & there are many rows in file1 with same key and i want to splice with the 2nd file to get the
sub agent records from 2nd file ..ie i am expecting the output for duplicate keys also
I reached in the forum but not able to get the exact solution for my problem.In our project JONKEYS are not used, only SPLICE tool is used for merging the files
==============
PMT.FILE1
==============
File1 contains duplicat keys i.e agent no is the key AG000056481 and i want to splice with the 2nd file to get the
sub agent records..ie i want the output for duplicate keys
In File1 key is from 55 position
1|12|P26108662PAC|20070702|20100702|20110702|20100702|AG000057030
1|12|P26137247PAC|20080929|20100929|20110929|20100929|AG000056481
1|12|P61432752PAC|19990601|20100601|20110601|20100601|AG000057046
1|12|P31407628PAC|20100318|20100318|20110318|20100318|AG000057112
1|12|P12623031PAC|20090805|20100805|20110805|20100805|AG000056515
1|12|P61413231PAC|19991227|20101227|20111227|20101227|AG000041004
1|12|P26135627PAC|20071204|20101204|20111204|20101204|AG000056481
1|12|P26041102PAC|20090406|20100406|20110406|20101208|AG000011335
1|12|P26118065PAC|20101201|20101201|20111201|20101201|AG000057071
1|12|P26048375PAC|20061201|20101201|20111201|20101201|AG000056481
1|12|P61443259PAC|20000116|20110116|20120116|20110116|AG000040001
==============
PMT.FILE2
==============
There ARE NO DUPLICATE KEY FIELDS in the 2nd file
It contains the Master agent and subagent, KEY IS Master Agent no
In File2 key is from 1 to 11 position and the remaining is subagent
AG000019701 |AG000019701
AG000095002 |AG000095002
AG000056158 |AG000056158
AG000030993 |AG000030993
AG000040090 |AG000092608
AG000072062 |AG000080060
AG000019652 |AG000019652
AG000056164 |AG000056164
AG000056781 |AG000080106
AG000043410 |AG000043410
AG000051645 |AG000051645
AG000056439 |AG000092608
AG000080052 |AG000080052
AG000019111 |AG000019111
AG000080124 |AG000080124
A9000042938 |AG000042938
When i Splice the output is as below (FOR the duplicate keys only one row is written i,.e AG000056481 has the
subagent only for last occurance, but i want the subagent for all the occurances
==================
OUTPUT FILE
=================
|12|P26041102PAC|20090406|20100406|20110406|20101208|AG000011335 |AG000092902
|12|P61443259PAC|20000116|20110116|20120116|20110116|AG000040001 |AG000040001
|12|P61413231PAC|19991227|20101227|20111227|20101227|AG000041004 |AG000080050
|15|P15176602PAC|20070406|20100406|20110406|20100406|AG000041363 |AG000041363
|15|P15214827PAC|20080917|20100917|20110917|20100917|AG000041727 |AG000041727
|12|P77067645PAC|19870601|20100612|20110612|20100612|AG000056268 |AG000092299
|12|P26137247PAC|20080929|20100929|20110929|20100929|AG000056481 (subagent is not populated)
|12|P26137247PAC|20080929|20100929|20110929|20100929|AG000056481 (subagent is not populated)
|12|P26137247PAC|20080929|20100929|20110929|20100929|AG000056481 |AG000056481
|12|P12623031PAC|20090805|20100805|20110805|20100805|AG000056515 |AG000080018
|12|P61420793PAC|19990617|20100617|20110617|20100617|AG000057021 |AG000080104
//STEP0001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=PMT.FILE1,DISP=SHR
//IN2 DD DSN=PMT.FILE2,DISP=SHR
//T1 DD DSN=DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(TRK,(5,5))
//OUT DD DSN=PMT.OUTFILE,DISP=OLD
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(55,11,CH) WITHALL -
WITH(55,24) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,66)
/*
//CTL2CNTL DD *
INREC BUILD=(55:1,24)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(1,400)
if possible please let me know how to use SPLICE tool If both FILE1 & FILE2 also contains duplicate keys.
Thanks
Prasad |
|