View previous topic :: View next topic
|
Author |
Message |
sarankriya
New User
Joined: 15 Apr 2020 Posts: 3 Location: India
|
|
|
|
Hi!
I have a requirement as below:
File 1:
//IN1 DD *
11111 000000 IN
22222 000000 UK
File 2:
//IN2 DD *
11111 333333 IN
11111 333331 IN
22222 444441 UK
22222 444442 UK
22222 444443 UK
My output should be below:
11111 000000 IN 333333 333331
22222 000000 UK 444441 444442 444443
Tried with icetool but getting below ouput:
11111 000000 IN333333
22222 000000 UK444441
It takes either only first or last occurence.
this is what i have used:
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT12) ON(1,5,ZD) WITHANY-
WITH(16,7) KEEPNODUPS
CTL1:INREC OVERLAY=(1,15,500Z)
CTL2: INREC OVERLAY=(1,5,16:7,6) |
|
Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015 Posts: 1222 Location: Bamberg, Germany
|
|
|
|
And you really think that someone is willing to help you providing only these little details? |
|
Back to top |
|
|
sarankriya
New User
Joined: 15 Apr 2020 Posts: 3 Location: India
|
|
|
|
Apologies for not providing sufficient details
file1 is of length 15 bytes
1-5 bytes-Branch code
7-12 bytes-Account no(will be always zeroes)
14-15 bytes-country code
file2 is of length 15 bytes
1-5 bytes-Branch code
7-12 bytes-Account no
14-15 bytes-country code
Branch code is the key from first file where i need to get all the matching account no from file2 and i need to write all matching account no in a single record
File 1:
//IN1 DD *
11111 000000 IN
22222 000000 UK
File 2:
//IN2 DD *
11111 333333 IN
11111 333331 IN
22222 444441 UK
22222 444442 UK
22222 444443 UK
My output should be below:
11111 000000 IN 333333 333331
22222 000000 UK 444441 444442 444443 |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
What if there are no records in dataset (not file) 2 for a branch code in dataset 1? And the same for accounts for a branch in dataset 2 but that branch does not appear in dataset 1? |
|
Back to top |
|
|
sarankriya
New User
Joined: 15 Apr 2020 Posts: 3 Location: India
|
|
|
|
Hi Nick,
File 2 is a master file.It would have all the combinations.
We have to match file 1 with file 2 to get in a single record.
If a record is in file2 but not in file1 ,thats nowhere needed |
|
Back to top |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
Hi Sarankriya,
I got similar requirement to achieve.
Did you find the answer for your query? If yes, could you please provide the solution?
Thanks,
Murali |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
murali.andaluri wrote: |
Hi Sarankriya,
I got similar requirement to achieve.
Did you find the answer for your query? If yes, could you please provide the solution?
Thanks,
Murali |
Both of you: try to use in Google the search string like 'SORT JOIN operation'
Google bless you! |
|
Back to top |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
Hi Sergeyken,
I tried with join keys, but couldn't find solution for Sort Join with three input files. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
murali.andaluri wrote: |
Hi Sergeyken,
I tried with join keys, but couldn't find solution for Sort Join with three input files. |
I do not believe you ever tried something on your own...
Please demonstrate, how did you use any solution with two datasets??!!
Sort Join solution on three datasets is just applying it to the first two files, and next - applying similar solution to the result of previous step, and the third dataset...
Unless you demonstrate that you really did something by yourself, I personally refuse to give you any working example. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
topic locked as it was hijacked |
|
Back to top |
|
|
|