View previous topic :: View next topic
|
Author |
Message |
Imrankhan
New User
Joined: 28 May 2007 Posts: 6 Location: Chennai
|
|
|
|
Hi,
How to replicate rows n times based on the key fields, consider two files one having duplicate keys and another one having unique key. I wanted to replicate records in unique file as many records present for that particular key from the duplicate file.
For eg:
FileA
Key|Fields
AAA|01
AAA|04
AAA|06
BBB|03
CCC|01
CCC|03
FileB
Key|Fields
AAA|10
BBB|12
CCC|09
Output should like:
Key|Field2
AAA|10 (AAA has 3 recs in the FileA, hence need to replicate 3 times)
AAA|10
AAA|10
BBB|12 (BBB has only 1 record in FileA)
CCC|09 (CCC has 2 records in FileA)
CCC|09 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
You have posted your question in the area for Job Vacancies. . .
In which part of the forum do you really want your topic located? |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Hi Dick,
I would like to "move" this topic to JCL part of the Froum until we "discover" what sort of solution (COBOL, SyncSort or DFSORT) he is looking for; as on homepage having this post titled as "Latest News: How to replicate rows n times based on the key" seems funny . . .
Hopefully it's not a bother . . .
Ad |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Anuj,
Not a bother
I'd hoped Imran would have replied much earlier and i'd have relocated the topic and removed my "guidance" post. |
|
Back to top |
|
|
Imrankhan
New User
Joined: 28 May 2007 Posts: 6 Location: Chennai
|
|
|
|
Hi All,
I wanted to place my query in JCL part using DFSORT. Actually i forgot to mention it in my question.. sorry for the inconvenience caused and as Dick mentioned it is for my Job requirement only.
Please kindly help me |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Imran,
Quote: |
I wanted to place my query in JCL part using DFSORT |
Please note that there is a JCL part of the forum (which is where Syncsort questions are placed) and there is a separate part of the forum for DFSORT & ICETOOL.
Your topic is now in the DFSORT part of the forum.
Quote: |
sorry for the inconvenience caused and as Dick mentioned it is for my Job requirement only. |
Not to worry, though careful reading of which topics go in which part of the forum will get you answers quicker. . . |
|
Back to top |
|
|
Imrankhan
New User
Joined: 28 May 2007 Posts: 6 Location: Chennai
|
|
|
|
Thank you so much Dick. From now on i'll post my query in correct Forum |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
isn't it a simple case of 2 file matching and overlaying the contents from fileb onto filea?
I assumed that your input is FB and lrecl of 80. The following DFSORT/ICETOOL jcl will give you the desired results. Make sure that you have disp=MOD for t1 dataset.
Also if there is a way to identify the records as to which file they belong and if both files are of the same LRECL, the same can be done with a single pass of data.
Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILEA DD *
AAA|01
AAA|04
AAA|06
BBB|03
CCC|01
CCC|03
//FILEB DD *
AAA|10
BBB|12
CCC|09
//T1 DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(FILEB) USING(CTL1)
COPY FROM(FILEA) USING(CTL2)
SORT FROM(T1) USING(CTL3)
//CTL1CNTL DD *
OUTFIL FNAMES=T1,OVERLAY=(81:C'1')
//CTL2CNTL DD *
OUTFIL FNAMES=T1,OVERLAY=(81:C'2')
//CTL3CNTL DD *
SORT FIELDS=(1,3,CH,A),EQUALS
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,ZD,EQ,1),PUSH=(5:5,2,82:1,3))
OUTFIL FNAMES=OUT,BUILD=(1,80),
INCLUDE=(81,1,ZD,EQ,2,AND,82,3,CH,EQ,1,3,CH)
/* |
|
|
Back to top |
|
|
|