SYNCSORT to compare and update a field in a file
Posted:
Thu Feb 11, 2010 9:34 pm
by RonaldCDcosta
Please help in resolving below errors:(there is some change in field position and are included in Joinkeys)
1) I'm getting below error. I think it is for '?'.
SYSIN :
JOINKEYS FILES=F1,FIELDS=(23,15,A)
JOINKEYS FILES=F2,FIELDS=(20,15,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,?,F2:50,6)
*
OPTION COPY
OUTFIL IFOUTLEN=80,
IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(39:82,6))
WER268A REFORMAT STATEMENT: SYNTAX ERROR
2) If i dont use '?' then i get below abend:
JOINKEYS REFORMAT RECORD LENGTH= 86, TYPE = F
SORTOUT OUTREC FIELD OUTSIDE RANGE
SYNCSMF CALLED BY SYNCSORT; RC=0000
Re: SYNCSORT to compare and update a field in a file
Posted:
Fri Feb 12, 2010 12:33 am
by RonaldCDcosta
Hi All,
I have 2 sequential files.
File1 = length 80, File2 = lenght 133
File 1:
..... 111111........AAAAA...
......222222........BBBBB...
File2:
.. 333333........CCCCC...
.. 222222........DDDDD...
I want to compare the position 20:6 of file1 with column 25:6 of file2. If match is found then update column(50:5) in FIle1 with column(45:5) from File 2.
i.e after comparing the above 2 files, the File1 must be updated as below:
File 1:
..... 111111........AAAAA... <------NO CHANGE AS NO MATCH IN FILE 2
......222222........DDDDD... <------ BBBBB UPDATED TO DDDDD AFTER COMPARING WITH FILE2.
I will be using SYNCSORT.
Re: SYNCSORT to compare and update a field in a file
Posted:
Fri Feb 12, 2010 1:52 am
by Alissa Margulies
Ronald,
REFORMAT FIELDS=(F1:1,80,?,F2:50,6)
Inserting a question mark (or any character string) in the REFORMAT statement is not supported.
Without the question mark, you can try changing your OVERLAY statement to:
OVERLAY=(39:81,6)) or OVERLAY=(39:82,5))
In regards to your second post, try this:
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(20,6,A)
JOINKEYS FILES=F2,FIELDS=(25,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,F2:45,5)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(45,5,CH,NE,C' '),
BUILD=(1,49,81,5,55,26)),
IFTHEN=(WHEN=NONE,
BUILD=(1,80))
/*
Re: SYNCSORT to compare and update a field in a file
Posted:
Fri Feb 12, 2010 10:36 pm
by RonaldCDcosta
The above requirement is little changed. Now the fileds to compare is 15 digits and position is as mentioned in JOINKEYS. I need to replace field of lenght 4 insted for 5.
Now the data types is also different as mentioned below.
File 1: column(39: ) ->S9(07) COMP-3 - Julian date(YYDAY)
File2: column(50:6) -> x(6) - YYYYMM - year+month
So we have to convert date of x(6) to Julian date of 9(7)comp3 and than replace. How can i handle this?
What changes i need in below SORT
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(23,15,A)
JOINKEYS FILES=F2,FIELDS=(20,15,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,80,F2:50,6)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(81,6,CH,NE,C' '),
BUILD=(1,38,81,6,45,36)),
IFTHEN=(WHEN=NONE,BUILD=(1,80))
//*
Code'd
Re: SYNCSORT to compare and update a field in a file
Posted:
Sun Feb 14, 2010 5:08 am
by RonaldCDcosta
The above solution picks up the date in YYYYMM format and copies in 2nd file when match found.
But this is not converting YYYYMM to Julian date. I need to first convert that date to Julian date and then copy the Julian date in the file. How to do that?
Re: SYNCSORT to compare and update a field in a file
Posted:
Mon Feb 15, 2010 8:31 pm
by Alissa Margulies
You can refer to this thread for converting Gregorian Dates to Julian Dates:
syncsort-synctool/topic2899.html
Re: SYNCSORT to compare and update a field in a file
Posted:
Sat Feb 20, 2010 7:48 am
by RonaldCDcosta
I have 3 doubts in the above logic of converting date to Julian. (refering to link)
Can you please explain the meaning of these lines:
1) how does this build work? what is 6X?
BUILD=(3,2,6X,9,52,C'N',1,8)),
2) where is this 61th position where we are writing 'L'? i understand that file is only of 60 length.
IFTHEN=(WHEN=(62,4,CH,EQ,L(C'1996',C'2000',C'2004',C'2008',
C'2012',C'2016',C'2020',C'2024',C'2028',C'2032',C'2036',C'2040',
C'2044',C'2048',C'2052',C'2056',C'2060',C'2064',C'2068',C'2072',
C'2076',C'2080',C'2084',C'2088',C'2092',C'2096',C'1992')),
OVERLAY=(61:C'L'),HIT=NEXT),
3) whats meaning of this : OUTREC FIELDS=(1,2,68,2,ZD,ADD,3,3,ZD,EDIT=(TTT),3X,9,52)
Re: SYNCSORT to compare and update a field in a file
Posted:
Mon Feb 22, 2010 8:58 pm
by Alissa Margulies
Ronald,
BUILD=(3,2,6X,9,52,C'N',1,8)),
1. 6X means insert 6 spaces. This BUILD statement writes out field (3,2), followed by 6 spaces, field (9,52), inserts character N, and then field (1,8).
2. The actual letter L is not the character string in the record, it is part of the syntax for the "list" shorthand specification.
In regards to the record length, the INREC statement (WHEN=INIT) extends the LRECL.
OUTREC FIELDS=(1,2,68,2,ZD,ADD,3,3,ZD,EDIT=(TTT),3X,9,52)
3. This statement writes out position 1 for length of 2, then at position 3, adds the ZD data from field (68,2) to field (3,3) and formats the data into 3 sigificant digits, 3 blanks are then inserted, and field (9,52) follows.
Hope this helps. Let me know if you have any further questions.