Position 01 thru 18 => Account Number (Length : 18 )
Position 19 thru 28 => Interest (Length : 09)
Position 29 thru 38 => Date 1 (Length : 10)
Position 39 thru 48 => Date 2 (Length : 10)
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
********************************* Top of Data **************
001500751530022852000000000 NA NA
001500751030000062010390560 NA NA
001500751030000130000000000 NA NA
001500751030000130009752040 NA NA
001500751030000229000000000 NA NA
001500751030000229010390560 NA NA
001510021530000643000000000 NA NA
001500751030000372008092920 NA NA
001500751030000400010208520 NA NA
001500751030000402000000000 NA NA
001500751030000402008463840 NA NA
001500751030000406000000000 NA NA
001500751030000406009568920 NA NA
001500751030000408006408360 NA NA
001500751030000415000000000 NA NA
001510021530049909005651280 NA NA
Expected Output File
Expecetd Data SHOULD BE in Position 49 thru 50 column wise. This is a Vertical Merge. When the first 18 position in Input File #1 and Input File #2 matches, the value in position 19 and 20 from (Input File #1) is written to output file in position 49,50. If there is NO match then write as NA. I made Position 01 thru 18 in rows 1, 7 and 16 to match and hence the value (position 18 and 19 column) in these rows from Input File #1 are written to the output file. Note that 1st Row in the Output File, position 49 and 50 is 01 and not 1, wanted to add that this postion should always be put with a leading 0 if this is a single digit.
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
********************************* Top of Data **************
001500751030000062000000000 NA NA 01
001500751030000062010390560 NA NA NA
001500751030000130000000000 NA NA NA
001500751030000130009752040 NA NA NA
001500751030000229000000000 NA NA NA
001500751030000229010390560 NA NA NA
001500751030000372000000000 NA NA 16
001500751030000372008092920 NA NA NA
001500751030000400010208520 NA NA NA
001500751030000402000000000 NA NA NA
001500751030000402008463840 NA NA NA
001500751030000406000000000 NA NA NA
001500751030000406009568920 NA NA NA
001500751030000408006408360 NA NA NA
001500751030000415000000000 NA NA NA
001500751030000415005651280 NA NA 10
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Fri Oct 26, 2007 3:18 pm Post subject:
Your output doesn't seem to match your input or rules. For example, I see the following match on an account number in input file1 and file2:
Code:
acct--------------
00151002153000064316
001510021530000643000000000 NA NA
and I would expect an output record for that account with the 16 vertically merged in as follows:
Code:
001510021530000643000000000 NA NA 16
But instead you have:
Code:
001500751030000372000000000 NA NA 16
which is a different record. Why?
You need to clear up the confusion. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
I know you have asked for a solution thru SORT. You can get the desired results using below EZT solution.
Code:
//EZT01 EXEC PGM=EZTPA00
//SYSPRINT DD SYSOUT=*
//SYSSNAP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=D
//EZTVFM DD UNIT=SYSDA,SPACE=(CYL,(5,1))
//*
//IN1 DD *
001500751530022852 1
001500751530022853 1
001500751530022854 1
001500751530022855 1
001500751530022856 1
001500751530022857 1
00151002153000064316
00151002153000064416
00151002153000064516
001510021530014752 2
001510021530015239 1
001510021530017287 1
001510021530024101 1
001510021530024602 7
00151002153002912114
00151002153004990910
//IN2 DD *
001500751530022852000000000 NA NA
001500751030000062010390560 NA NA
001500751030000130000000000 NA NA
001500751030000130009752040 NA NA
001500751030000229000000000 NA NA
001500751030000229010390560 NA NA
001510021530000643000000000 NA NA
001500751030000372008092920 NA NA
001500751030000400010208520 NA NA
001500751030000402000000000 NA NA
001500751030000402008463840 NA NA
001500751030000406000000000 NA NA
001500751030000406009568920 NA NA
001500751030000408006408360 NA NA
001500751030000415000000000 NA NA
001510021530049909005651280 NA NA
//OUT1 DD DSN=A.B.C,DISP=SHR
//SYSIN DD *
FILE IN1
IN1-AC-NBR 001 018 N
IN1-PAY-DAY 019 002 N
FILE IN2
IN2-AC-NBR 001 018 N
IN2-INTEREST 019 009 N
IN2-DATE1 029 010 N
IN2-DATE2 039 010 N
FILE OUT1
OUT1-AC-NBR 001 018 N
OUT1-INTEREST 019 009 N
OUT1-VALUE1 029 002 A
OUT1-VALUE2 039 002 A
OUT1-VALUE3 049 002 A
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
001500751530022852 1
001500751530022853 1
001500751530022854 1
001500751530022855 1
001500751530022856 1
001500751530022857 1
00151002153000064316
00151002153000064416
00151002153000064516
001510021530014752 2
001510021530015239 1
001510021530017287 1
001510021530024101 1
001510021530024602 7
00151002153002912114
00151002153004990910
//IN2 DD *
001500751530022852000000000 NA NA
001500751030000062010390560 NA NA
001500751030000130000000000 NA NA
001500751030000130009752040 NA NA
001500751030000229000000000 NA NA
001500751030000229010390560 NA NA
001510021530000643000000000 NA NA
001500751030000372008092920 NA NA
001500751030000400010208520 NA NA
001500751030000402000000000 NA NA
001500751030000402008463840 NA NA
001500751030000406000000000 NA NA
001500751030000406009568920 NA NA
001500751030000408006408360 NA NA
001500751030000415000000000 NA NA
001510021530049909005651280 NA NA
//T1 DD DSN=&T1,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//T2 DD DSN=&T2,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(101,8,PD) WITH(21,65) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)
//CTL1CNTL DD *
OUTREC FIELDS=(1:1,20,101:SEQNUM,8,PD)
//CTL2CNTL DD *
OUTREC FIELDS=(21:1,65,101:SEQNUM,8,PD)
//CTL3CNTL DD *
OUTFIL FNAMES=T2,OUTREC=(1,80)
/*
//CTL4CNTL DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(71:C'NA')),
IFTHEN=(WHEN=(1,18,CH,EQ,21,18,CH),
OVERLAY=(71:19,2,ZD,EDIT=(TT)))
OUTREC FIELDS=(1:21,60)
/*
Cannot think of a solution with less no of passes. _________________ Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
Thank you for your EZT solution. I know this works in our shop. But I have NOT worked with EZT before. But the solution is more understandable. I will give a try with EZT as well.
I have some difficulty when trying to use with files instead of instream data. Maybethe postion is what matters.
Input File #1 is FB, LRECL = 20
Input File #2 is FB, LRECL = 50
Output File is FB, LRECL = 50
I am quite confused on the positions that were used in various control cards.
CTL1 - Writes from IN1 to a T1 (Temp File) from positions 1 till 20. I am NOT clear on the usage of 101:SEQNUM,8,PD
CTL2 - Writes from IN2 to a T2 (Temp File) from positions 21 till 65. I am NOT clear on the usage of 101:SEQNUM,8,PD. Instead of 65 I used 50 which is the actual length of file.
48 is the exact column till where we have the data from Input File #2.
CTL4 - I believe 71 is a position used to hold either NA or the actual value from Input File #1. And we then write to OUT file. I used 49 instead of 71.
But the output does not comes as it comes with the instream data.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Mon Oct 29, 2007 12:21 pm Post subject:
sakreg,
As I said previously, your output doesn't seem to match your input for your example.
If you want MY help (and maybe you don't), please show the correct expected output records for your example input records. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
I have some difficulty when trying to use with files instead of instream data. Maybethe postion is what matters.
Input File #1 is FB, LRECL = 20
Input File #2 is FB, LRECL = 50
Output File is FB, LRECL = 50
I am quite confused on the positions that were used in various control cards.
Quote:
CTL1 - Writes from IN1 to a T1 (Temp File) from positions 1 till 20. I am NOT clear on the usage of 101:SEQNUM,8,PD
The sort cards deals with Temp files and hence the lengths of the temp files are used for work-around purpose. The actual length is to be dealt with the CTL4 which uses the actual output file.
Make the following changes in the CTL4:
1. Instead of 71 give 69. (Coz the output u require should have 'NA' in position 49. )
2. Change the outrec fields = (1:21,50). (This will write the output file which is length 50, starting from position 21 from the temp file).
By making these changes you can achieve the output u require.
There are lots of examples on the usage of temp files in sort techniques. You could use them as a reference.
But then.. I would suggest you provide the actual input and the matching desired output so that you can get improved solutions to the actual problem. _________________ Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
I got what I want. I would be happy if you can explain the usage of overlay. I thought I should use 49, Why are we using 69 to get the actual output at 49?
thought I should use 49, Why are we using 69 to get the actual output at 49?
In the temp file (after the 3rd pass), the actual or desired output will be after the 20th position. We will be just extracting from this temp file after excluding the first 20 characters to the output file.
The quick manuals link at the top of this page will be very useful to learn the various applications of SORT techniques. _________________ Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
