View previous topic :: View next topic
|
Author |
Message |
hiravibk Warnings : 1 Active User
Joined: 14 Dec 2008 Posts: 107 Location: India
|
|
|
|
Hi,
I have the following two files:
1) Trans file: Record length is 660 and FB.
Product-Key-ID - Is the key field. Position 44(Length - 4 bytes)
FILLER - Position 610 (Length - 51 bytes)
File contains data in other positions
Eg.
Code: |
44th Position 610th position
1234...0012.......................<SPACES>
9578...0034.......................<SPACES>
|
2)Lookup-file: Record length is 61 and VB.
Product-Key-ID - Is the key field. Position 1(Length - 4 bytes)
Product-Description - Position 7(Length - 50 bytes)
Eg
Code: |
0012...INSURANCE
0034...MORTGAGE
|
I need to look up the product-key-id for each record in Trans-file in the Lookup-file, get the corresponding product description and splice it at position 610 in the Trans-file.
Required Output:
Code: |
1234...0012....INSURANCE
9578...0034....MORTGAGE
|
Code:
Code: |
//TRANSFIL DD DSN = Trans-file(LRECL=660,RECFM=FB), DISP=SHR
//LOOKUP DD DSN = Lookup-file(LRECL=61,RECFM=VB), DISP=SHR
//T1 DD DSN = TEMP.FILE, DISP=(MOD,PASS),
// DCB=(RECFM=FB,LRECL=660,BLKSIZE=27720)
//OUTPUT DD DSN = OUTPUT.FILE(LRECL=660,RECFM=FB), DISP=(NEW,CATLG,DELETE)
//TOOLIN DD *
COPY FROM(TRANSFIL) TO(T1) USING(CTL2)
COPY FROM(LOOKUP) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUTPUT) ON (44,5,BI) - WITH(610,50)
//CTL1CNTL DD *
SORT FIELDS = COPY
/*
//CTL2CNTL DD*
OUTFIL FNAMES=T1,VTOF,BUILD(43X,44:5,4,610:X,611:11,50)
/*
|
Issue: Not all records in the TRANSFIL are present in the O/P and the product description is not coming up in O/P file.
Please help. Thank you. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Other than if your DFSORT is way out of service, why are you using SPLICE for this not JOINKEYS? Life's much simpler and more efficient with JOINKEYS. |
|
Back to top |
|
|
hiravibk Warnings : 1 Active User
Joined: 14 Dec 2008 Posts: 107 Location: India
|
|
|
|
Thanks Bill. I had not used joinkeys before, hence was trying with splice. |
|
Back to top |
|
|
hiravibk Warnings : 1 Active User
Joined: 14 Dec 2008 Posts: 107 Location: India
|
|
|
|
Anybody on how this can be achieved using joinkeys? Thanks in advance. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
You need one JOINKEYS statement specifying the key, the other the key on the lookup. If the lookup is in sequence, specify SORTED,NOSEQCK on that JOINKEYS.
You will need a REFORMAT statement. Exactly what that looks like depends on whether you want to replace anything that exists on the main file with the lookup data, or whether you only want to replace in certain situations, and what you want to do when there is no match.
Presumably you then need a SORT in the Main Task to get the main file back into the original order. Ensure that you specify EQAULS for that SORT. The original data has a key? |
|
Back to top |
|
|
|