|
View previous topic :: View next topic
|
| Author |
Message |
Ramsee
New User
.jpg)
Joined: 06 Jan 2011
Posts: 53
Location: Chennai
|
|
|
|
Hi,
I need to compare two files (A & B) i should replace the FILE A with FILE B when the keys are equal else i should not replace the FILEA, it should remain as it is.
File A Key Positions 1-10 and 16-23
File B Key Positions 1-10 and 11-18
The data to be replaced is 11-15 positions in FILE A from FILE B Positions 19-23
I am using SYNC SORT i tried using the following SORTCARD:
JOINKEYS FILE=F1,FIELDS=(1,10,A,16,8,A)
JOINKEYS FILE=F2,FIELDS=(1,10,A,11,8,A)
REFORMAT FIELDS=(F1:1,23,F2:1,23)
JOIN UNPAIRED,F1
SORT FIELDS=COPY
OUTREC FIELDS=(1:1,10,11:42,5,16:16,8)
As a result i am getting Spaces in the 11-15 Positions.
Sample input
FILE A
10443030019999900003333
10443030019999900003333
10443030019999900003333
FILE B
10443030010000333300038
10443030010000333312345
10443030014444333334344
Sample Output:
10443030010003800003333
10443030011234500003333
10443030019999900003333
Please guide me on this issue. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Please post the jcl you submitted and the output data file created. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Isn't it going to be the records on FILE A which do not match FILE B?
Your JOIN gives you all the paired records, plus unpaired records from FILE A. For the paired records, the REFORMAT will be processed, for the unpaired records not. Then the OUTREC will find data for the records which have been REFORMATted but just spaces for the unpaired records.
Anyway, samples of input data which would show matching and non matching conditions, output data for that, and JCL as Dick has asked already. |
|
| Back to top |
|
|
Ramsee
New User
Joined: 06 Jan 2011
Posts: 53
Location: Chennai
|
|
|
|
Hi All,
Sorry for the delay, Please find the JCL Details,
//S01S EXEC PGM=SORT
//**********************************************************************
//* SORT TO REPLACE FILE 2 DATA WITH FILE1 DATA
//**********************************************************************
//SORTJNF1 DD DSN=TST.FILE1.DATA,DISP=SHR
//SORTJNF2 DD DSN=TST.FILE2.DATA,DISP=SHR
//SORTOUT DD DSN=TST.FILE3.DATA.REPLACE,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(27996,(1000,100),RLSE),
// LABEL=RETPD=180,VOL=(,RETAIN,,111)
//STATOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,10,A,16,8,A)
JOINKEYS FILE=F2,FIELDS=(1,10,A,11,8,A)
REFORMAT FIELDS=(F1:1,23,F2:1,23)
JOIN UNPAIRED,F1 ONLY
SORT FIELDS=COPY
OUTREC FIELDS=(1:1,10,11:42,5,16:16,8)
/*
Output of the above JCL:
1008575001 00003338
1008575008 00003338
1015195003 00003338
1015195005 00003338
1015238001 00003338
1015238003 00003338
1021063001 00003338
1021063002 00003338
1033551001 00003338
1033551002 00003338
1036006001 00003338
1036006002 00003338
1040509001 00003338
1040509002 00003338
1040509003 00003338
1040513002 00003338
1040514001 00003338
Please let me know how to resolve this issue in a single SORTCARD. |
|
| Back to top |
|
|
Ramsee
New User
Joined: 06 Jan 2011
Posts: 53
Location: Chennai
|
|
|
|
Output have spaces which is not highlighted correctly.
1008575001bbbbb00003338
1008575008bbbbb00003338
1015195003bbbbb00003338
Note:
bbbbb --> Blank spaces |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Note: using code tags retains spaces so that you do not have to do what you just did. See the button labeled 'Code' just above the message body box? Highlight the text to be 'coded' and click on that button. But you knew that because it has been mentioned so many times recently and you do read everyone's posts, don't you, just in case you learn something useful or find the answer to your query without having to post anything? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Where is the input data from the two files that created this output? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I don't have Syncsort documentation, but you do. Do all the records contain blanks? Or only those that do not match between F1 and F2? If the latter, check the following:
| Ramsee wrote: |
[...]
| Code: |
JOIN UNPAIRED,F1 ONLY
|
[...]
|
Your "ONLY" above is a comment. If you put a comma so that it is part of the statement, then you will only get those from F1 which do not match F2. Ditch it.
If F1 matches F2 on the keys, you want to update the position 11 length 5 with data from F2. If F1 does not match F2 you want to leave the F1 record unchanged in the output. Is that correct?
Your OUTREC is processing all output records. So messing-up your unmatched F1 records, the F2 value for those having been set to the "fill" character, presumably space.
In the REFORMAT you can specify a field of ?, which will include at that position an indicator, B if matched on both files, 1 if unmatched from F1 and 2 if unmatched from F2.
In the OUTREC you can include IFTHEN=(WHEN and test the indicator. Only do that build for those that are B. For the 1s just BUILD with the original F1 that you have stored in the REFORMAT.
That should give you what you want, but you'll have to check yourself. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Ramsee,
This post supposed to be posted in DFSORT forum.
Anyways Change your OUTREC statement as below and see if it works
| Code: |
OUTREC IFTHEN=(WHEN=(24,1,CH,NE,X'40'),
BUILD=(1:1,10,11:42,5,16:16,8)),
IFTHEN=(WHEN=(24,1,CH,EQ,X'40'),
BUILD=(1:1,23)) |
Here I am assuming that if you dont find match on file 2 then felds from file 2 will be spaces.
Also your matchging kely will never have spaces iin the begining. If it is the case then you may need to have FILL character as per suggested by Bill.
Kindly let us know if it works for you.
Regards,
Chandan |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Why might you believe this should be posted in the DFSORT part of the forum 
TS posted Syncsort is being used and Syncsort topics are discussed in this (JCL) part of the forum . . . |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| oops..my bad.. I am sorry Dick.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Not a problem - no harm, no foul 
d |
|
| Back to top |
|
|
Ramsee
New User
Joined: 06 Jan 2011
Posts: 53
Location: Chennai
|
|
|
|
Thanks a lot Chandan,Bill,Dick for your suggesstions, the below SORTCARD was used for my requirement.
JOINKEYS FILE=F1,FIELDS=(01,10,A,16,08,A)
JOINKEYS FILE=F2,FIELDS=(01,10,A,11,08,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,23,F2:19,5)
INREC IFTHEN=(WHEN=(24,1,CH,EQ,C' '),BUILD=(1,23)),
IFTHEN=(WHEN=(24,1,CH,NE,C' '),BUILD=(1,10,24,5,16,8))
SORT FIELDS=COPY |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Ramsee wrote: |
Thanks a lot Chandan,Bill,Dick for your suggesstions, the below SORTCARD was used for my requirement.
JOINKEYS FILE=F1,FIELDS=(01,10,A,16,08,A)
JOINKEYS FILE=F2,FIELDS=(01,10,A,11,08,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,23,F2:19,5)
INREC IFTHEN=(WHEN=(24,1,CH,EQ,C' '),BUILD=(1,23)),
IFTHEN=(WHEN=(24,1,CH,NE,C' '),BUILD=(1,10,24,5,16,8))
SORT FIELDS=COPY |
Thanks for letting us know.
You could try something like the following. I have used SYMNAMES to make it self-documenting. You can use more meaningful names than I, as you know the data. You can even give them the same names as Cobol or other languages. I am assuming that SYNCSORT has the ? and WHEN=NONE.
The problem with relying on the FILL character might not bite you in your example, but if you then copy those sort cards for another requirement where the field you are testing can validly contain the fill character, then you have a mess.
I have relocated your "SORT" statement to a more common position, as that is where most people will look for it to be. It will not affect the processing. The order you put the cards in does not affect the order that SORT will process them in (assuming valid syntactically).
| Code: |
//SYMNAMES DD *
F1-RECORD,1,23,CH
F1-KEY-1,01,10,CH
F1-KEY-2,16,08,CH
F2-KEY-1,01,10,CH
F2-KEY-2,11,08,CH
F2-EXTRACT-DATA,19,5,CH
REF-F1-RECORD,1,23,CH
REF-JOIN-INDICATOR,29,1,CH
REF-F1-FIRST-10,1,10,CH
REF-F1-LAST-8,16,8,CH
REF-F2-EXTRACT-DATA,24,5,CH
RECORD-FROM-UNMATCHED-F1,C'1'
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILE=F1,FIELDS=(F1-KEY-1,F1-KEY-2)
JOINKEYS FILE=F2,FIELDS=(F2-KEY-1,F2-KEY-2)
JOIN UNPAIRED,F1
* The ? below inserts the join indicator, B=on both, 1=F1 only, 2=F2 only (you'll not get those).
*
REFORMAT FIELDS=(F1:F1-RECORD,F2:F2-EXTRACT-DATA,?)
INREC IFTHEN=(WHEN=(REF-JOIN-INDICATOR,
EQ,RECORD-FROM-UNMATCHED-F1),
BUILD=(REF-F1-RECORD)),
IFTHEN=(WHEN=NONE,
BUILD=(REF-F1-FIRST-10,
REF-F2-EXTRACT-DATA
REF-F1-LAST-8))
|
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
