| View previous topic :: View next topic |
| Author |
Message |
tempuser
Beginner
Joined: 05 Oct 2005
Posts: 28
Topics: 20
Location: INDORE
|
 Posted: Wed Oct 05, 2005 1:26 pm Post subject: Sync Sort:Select records from 2 files |
 |
|
Hi,
How can I get the details from 2 files one each from 2 files.
My first file is like this:
| Code: |
1234 a 2345
1234 b 2345
|
Second file is :
| Code: |
1234 a 2345
1234 a 2345
|
I want to create an output file like
| Code: |
1234 a 2345
1234 a 2345
1234 b 2345
1234 a 2345
|
the key field is the last field(2345).Here I need to select the first record from 1 file then 1 record from second file again 3 record from 1 file and then second record from second file
Thank you |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Oct 05, 2005 2:19 pm Post subject: |
|
|
tempuser,
| Quote: |
Here I need to select the first record from 1 file then 1 record from second file again 3 record from 1 file and then second record from second file
|
If you always have pairs(2 records in file1 and 2 in file2) then it is very easy. Do you have more than 2 records per key ? Also tell us the DCB parameters of both files and position of the key field.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
tempuser
Beginner
Joined: 05 Oct 2005
Posts: 28
Topics: 20
Location: INDORE
|
| Posted: Wed Oct 05, 2005 11:17 pm Post subject: |
|
|
Hi Kolusu,
I am going to have pairs always.ie if the first i/p file is going to have 100 records then the second one also consites of 100 only.The format is FB of length 80.The key position is 18 of length 4.
| Code: |
----+----1----+----2-
1234 A 2345
1234 B 2345
|
Thanks |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Thu Oct 06, 2005 1:04 am Post subject: |
|
|
Tempuser,
The following solution should do the job for you. If you are using DFSORT Change SYNCTOOL to ICETOOL
| Code: |
//R010 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//INPUT1 DD *
1111 A 2345
1111 B 2345
/*
//INPUT2 DD *
2222 A 2345
2222 B 2345
/*
//TEMP1 DD DSN=&&T1,DISP=(,PASS)
//TEMP2 DD DSN=&&T2,DISP=(,PASS)
//CONCAT DD DSN=&&T1,DISP=SHR,VOL=REF=*.TEMP1
// DD DSN=&&T2,DISP=SHR,VOL=REF=*.TEMP2
//OUTPUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(INPUT1) TO(TEMP1) USING(CTL1)
COPY FROM(INPUT2) TO(TEMP2) USING(CTL1)
SORT FROM(CONCAT) TO(OUTPUT) USING(CTL2)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
OPTION EQUALS
SORT FIELDS=(81,8,ZD,A)
OUTREC FIELDS=(1,80)
/*
|
In the above code, I am adding a 8 digit sequence number at the end of your record layout for both the files.
Later I sort on this sequence number. This way Seqnum 1s will be grouped together, similarly 2s and 3s and so on.
I you need any help understanding the solution pls let us know.
Hope this helps,
Thanks,
Phantom
Last edited by Phantom on Thu Oct 06, 2005 5:02 am; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Oct 06, 2005 4:36 am Post subject: |
|
|
Phantom,
I don' think your solution will fit the requirements. read this
| Quote: |
Here I need to select the first record from 1 file then 1 record from second file again 3 record from 1 file and then second record from second file
|
so you need to change your sort cards.
| Code: |
//TOOLIN DD *
COPY FROM(INPUT1) TO(TEMP1) USING(CTL1)
COPY FROM(INPUT2) TO(TEMP2) USING(CTL2)
SORT FROM(CONCAT) TO(OUTPUT) USING(CTL3)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,SEQNUM,8,ZD,START=1,INCR=2)
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,80,SEQNUM,8,ZD,START=2,INCR=2)
/*
//CTL3CNTL DD *
OPTION EQUALS
SORT FIELDS=(18,4,CH,A,81,8,CH,A)
OUTREC FIELDS=(1,80)
/*
|
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Thu Oct 06, 2005 5:00 am Post subject: |
|
|
Kolusu,
I have Two points.
1. I am not clear with the requirement.
| Quote: |
again 3 record from 1 file and then second record from second file
|
I thought this was a typo (3 record). Also, the test data that he has provided is not clear. Looking at the input and output there is no way to determine which record is taken from which file.
2. When the keys (18,4) are same - What is the difference in output b/w your solution and mine ? Both seem to group records by order of occurrence in their respective files
| Code: |
File 1 - Rec 1
File 2 - Rec 1
File 1 - Rec 2
File 2 - Rec 2
File 1 - Rec 3
File 2 - Rec 3
|
Am I missing something very basic over here ???
Tempuser,
Please clarify your requirement.
Thanks,
Phantom |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Thu Oct 06, 2005 5:06 am Post subject: |
|
|
Kolusu,
This the output I get when I ran your code. (Position of Sequence number has been changed from 80 to 30 for testing)
| Code: |
1111 A 2345 00000001
2222 A 2345 00000002
1111 B 2345 00000003
2222 B 2345 00000004
|
And this is my output.
| Code: |
1111 A 2345 00000001
2222 A 2345 00000001
1111 B 2345 00000002
2222 B 2345 00000002
|
Could you please highlight, where there would be a difference b/w these two solutions ?
Thanks,
Phantom |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Oct 06, 2005 5:14 am Post subject: |
|
|
From my understanding,
In1:
| Code: |
FILE1-REC1 2345
FILE1-REC2 2345
|
In2:
| Code: |
IN2-REC1 2345
IN2-REC2 2345
|
expected output
| Code: |
FILE1-REC1 2345
IN2-REC1 2345
FILE1-REC2 2345
IN2-REC2 2345
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Thu Oct 06, 2005 5:25 am Post subject: |
|
|
Exactly, Isn't the same result that we both got ???
Pls look the output. (This is mine). To differentiate records b/w file 1 and file 2, I used 1111 for all records from File 1 and 2222 for all records from file 2.
| Code: |
1111 A 2345 00000001
2222 A 2345 00000001
1111 B 2345 00000002
2222 B 2345 00000002
|
I get the same output when I use your code, except that the way the sequence numbers are handled by two of us.
| Code: |
1111 A 2345 00000001
2222 A 2345 00000002
1111 B 2345 00000003
2222 B 2345 00000004
|
I used grouping by sequence number SETS (1-1, 2-2, 3-3, 4-4 ...) and you grouped records by increasing order of sequence numbers throughout.
Do you see any difference here ?
Thanks,
Phantom |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Oct 06, 2005 5:46 am Post subject: |
|
|
Phantom,
Techincally both of our solutions will fail if the records are not sorted on the key (18,5,ch)
Your solution assumes that you club in2 records into in1 records alternatively irrespective of the key.
My solution took care of the key but not the sequence of the records.
May we should wait until OP comes back with correct information
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Thu Oct 06, 2005 5:56 am Post subject: |
|
|
Sure Kolusu, I agree with you.
Tempuser,
| Quote: |
I am going to have pairs always.ie if the first i/p file is going to have 100 records then the second one also consites of 100 only.The format is FB of length 80.The key position is 18 of length 4.
|
You said you always have pairs and also said key starts from Column 18 and is of length 4. Since the key is not unique, we do not understand your statement "always have pairs". Please provide correct and clear requirement.
Thanks,
Phantom |
|
| Back to top |
|
|
tempuser
Beginner
Joined: 05 Oct 2005
Posts: 28
Topics: 20
Location: INDORE
|
| Posted: Thu Oct 06, 2005 1:14 pm Post subject: |
|
|
Thank you Phantom and Kolusu.Sorry for not providing the correct test data.
| Quote: |
Here I need to select the first record from 1 file then 1 record from second file again 3 record from 1 file and then second record from second file
|
As you said it's typo.It should be second record from first file then second record from second file.
I tried the seqnum option before posting my query here.There I got some diffrent o/p ie..when the sequence number matches ,it's going to consider the remaining coulumn positions for sorting and is putting in the o/p file.
For example
If my first file is like below
1234 a 3456
1234 d 3456
and second file is like below.
1234 b 3456
1234 c 3456
I am getting the o/p as
1234 a 3456
1234 b 3456
1234 c 3456
1234 d 3456
Where as I am expecting the o/p
1234 a 3456
1234 b 3456
1234 d 3456
1234 c 3456
any way I will try the SEQNUM ottion tomorrow(since It is night here ,I can't test it) and I will check again.
I think Kolusu's solution will fit to my requrement exactly.
Thank you again Kolusu and Phantom. |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Fri Oct 07, 2005 12:09 am Post subject: |
|
|
Tempuser,
| Quote: |
am getting the o/p as
|
| Code: |
1234 a 3456
1234 b 3456
1234 c 3456
1234 d 3456
|
None of our solutions will give this output. Can you show us the sort card you used. My code sorts only on Seqnum with OPTION EQUALS set on. When there is a duplicate sequence number, the order of records will be preserved as it is in the input. Since, I concatenate file 1 followed by file 2, 'D' (File 1) will definitely come first followed by 'C' (file 2).
Thanks,
Phantom |
|
| Back to top |
|
|
bprasanna
Beginner
Joined: 05 Sep 2003
Posts: 119
Topics: 33
Location: Hyderabad
|
| Posted: Fri Oct 07, 2005 9:51 am Post subject: |
|
|
Tempuser,
U might be missing the OPTION EQUALS keyword in your sort step.Please check.
Thanks |
|
| Back to top |
|
|
tempuser
Beginner
Joined: 05 Oct 2005
Posts: 28
Topics: 20
Location: INDORE
|
| Posted: Fri Oct 07, 2005 11:26 am Post subject: |
|
|
Thank you for all your suggestions.I forgot to use the OPTION EQUALS command in my SORT step.
Thank you |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
