Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi All,
I need suggestion/guidance on below requirement.
My application will receive a file from third party. Now the main issue is they are not following correct record sequence always (except Header and Trailer). The example of correct record sequence should be given below..
Code:
HD <CCYYMMDD> <VER> ........ (HEADER Record)
D1 A123 <detail record info>
A0 A123 <Asset record info>
T0 A123 <Tax info1>
T0 A123 <Tax info2>
D2 C345 <Detail record info>
A0 C345 <Asset record info>
T0 C345 <Tax info1>
:
;
TR <CCYYMMDD> <D1 count> <D2 count> <A0 count> <T0 count>
Here in above format. The sequence should be D1/D2 then A0 and T0's. One D1/D2 record can have one A0 and one or many T0 records but they all should have same key example - A123 or C345.
Then file should have only one Trailer record here it is TR in the end.
Now the thrid party can send the same above file in the same sequence (given above) or they are allowed to send like below (one of the example)
Means except the Header and Trailer details records can be shuffled among themselves.
Code:
HD <CCYYMMDD> <VER> ........ (HEADER Record)
D1 A123 <detail record info>
D2 C345 <Detail record info>
A0 A123 <Asset record info>
T0 A123 <Tax info1>
A0 C345 <Asset record info>
T0 A123 <Tax info2>
T0 C345 <Tax info1>
So before passing this file as input to one of our data validation programs we are thinking to sort the file and make it correct sequence order. To do that I was thinking to take help of DFSORT. Now what they are asking if we can
1. Separate the Header and Trailer into one file
2. Correctly sorted sequenced data into another file.
To move the Header and Trailer into another file I guess we can do that using DFSORT OUTFIL FILES and INCLUDE (check for HD and TR) and for detail record OUTFIL FILES and INCLUDE (check for D1/D2/A0/T0).
But not getting on how can I create the correct sequence while copying the data records into another file. I mean D1/A0/T0/T0 then D2/A0/T0.
I can use key (here A123, C345 etc.) and record type (I mean D1/D2/A0/T0) but I guess then it would sort in either ascending or descending order (means..
instead of D1/A0/T0/T0 it would be A0/D1/T0/T0).
On how to achieve in one SORT card could someone please suggest me way. I apologize for such a big mail. Not sure if I have missed any info. But for better clarification please let me know I will try to provide.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Quote:
I apologize for such a big mail
You don't need to, This is how it should be. You've shown an example on how to post a query with as much information in the first post itself. Good job.
Coming to your question, this should be pretty easy, if I understood correctly. Use INREC IFTHEN and append sequence numbers at the end of your record like this
Code:
D1/D2 - '1'
A0 - '2'
T0 - '3'
While sorting, include the appended sequence number also in your sort fields along with the 'key'. In the OUTFIL/OUTREC BUILD, discard the appended field.
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
Hi Arun,
Thanks for your reply. Before I saw your post below is what I was trying to do..
Code:
STEP01 - in STEP01 I am changing all D1=01,D2=02,A0=04,T0=05
;
;
//TOOLIN DD *
DATASORT FROM(IN1) TO(OUT1) HEADER(1) TRAILER(1) USING(CTL1)
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=(1,2,CH,EQ,C'D1'),OVERLAY=(1:C'01')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'D2'),OVERLAY=(1:C'02')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'A0'),OVERLAY=(1:C'04')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'T0'),OVERLAY=(1:C'05'))
SORT FIELDS=(3,4,CH,A,1,2,CH,A) --> sorting on key (here A123, C345)
& record type (D1/D2/A0/T0)
STEP02 - Here I am changing them back (01=D1,02=D2,04=A0 etc.)
;
;
//SYSIN DD *
INREC IFTHEN=(WHEN=(1,2,CH,EQ,C'01'),OVERLAY=(1:C'D1')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'02'),OVERLAY=(1:C'D2')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'04'),OVERLAY=(1:C'A0')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'05'),OVERLAY=(1:C'T0'))
SORT FIELDS=COPY
My SORT approach may sound very dumb. Though I got the output but was thinking if I can do the same in one sort card (in one JCL step) as per your suggestion.
Could you please suggest me on the same? I mean I tried to use INREAC stmts first (for changing D1 to 01 etc.)then SORT details and then OUTREC Stmts (to change them back from 01 to D1 etc.)
NOTE - I thought to keep Header and Trailer intact for now and 03 I am not using because we are expecting any type of record which is D3.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
subratarec,
I thought you can have only D1 or D2, for a given key, not both. If that is the case, you can assign the same number '01' to both D1 and D2.
Something like...
And instead of overlaying the numbers on to your existing ID field at pos 1-2, add it to the end of your record, so you won't lose the data at pos 1-2. Then in an OUTFIL just get rid of the appended data. That would save you the extra COPY operation to put the original IDs back.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
You're welcome.
You could also try using ALTSEQ to consider 'D' (X'C4') before 'A' (X'C1') in the collating sequence. And please note the key in your sample data starts at pos-4, you might need to change the sort field starting position accordingly.
