IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Add PD field from 2nd file to PD in 1st


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Sushant Garje

New User


Joined: 01 Jul 2016
Posts: 4
Location: India

PostPosted: Thu Dec 01, 2016 4:32 pm
Reply with quote

Hi,

I have two files of 30 bytes as below

File 1
Code:

----+----1----+----2----+----3
ABCDANT XYZJE   .....àé.   HKD
ABCDANT XYZJE   ....r..@   USD
EFGHK   UVWNDAK .....Ì.%   USD
EFGHK   UVWNDAK ........   YEN
IJKLAM  RSTKUR  ....è...   HKD
MNOPN   OPQANI  ...gÁä..   AUD


File 2
Code:

----+----1----+----2----+----3
ABCDANT XYZJE   ........   HKD
ABCDANT XYZJE   .......<   USD
EFGHK   UVWNDAK .......%   USD
IJKLAM  RSTKUR  ........   HKD
MNOPN   OPQANI  .....àñ.   AUD
MNOPN   OPQANI  .....íé.   INR


Both files are in the same format.
The positions 17 to 24 contain the Amount in Packed Decimal format.
Code:

--------- FIELD LEVEL/NAME ---------- -PICTURE- -NUMBER START     END  LENGTH
SUM-TEST-INPUT                                              1      30      30
10 TEST-NAME                          X(15)          1      1      15      15
10 F1                                 X              2     16      16       1
10 TEST-AMT                           S9(13)V99      3     17      24       8
10 F2                                 XXX            4     25      27       3
10 TEST-CRNCY                         XXX            5     28      30       3


Query: My ask is to have an output file where Amounts from both files are added based on the key in positions 1 to 15 (name) & 28 to 30 (Currency) and the output file should be in the same format as the File 1.

Note:
- The File 1 is the driver file.
- File 2 may not contain all names from File 1.
- When above such scenario occurs, the record from File 1 should be written to output as it is. (i.e. adding a zero to the amount field).

I think I should use SORT JOIN to match the two files and SUM FIELDS for adding the Amounts but not sure how.

Can someone help me with the JCL SORT card?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Dec 01, 2016 5:38 pm
Reply with quote

Yes, it will be a JOINKEYS. No you need not use SUM.

Is your data already in key order? If it is not, do you want the output in key order, original order, or don't really care?
Back to top
View user's profile Send private message
Sushant Garje

New User


Joined: 01 Jul 2016
Posts: 4
Location: India

PostPosted: Thu Dec 01, 2016 6:32 pm
Reply with quote

Hi Bill,

The files would already be in sorted order of the keys fields.
Back to top
View user's profile Send private message
Sushant Garje

New User


Joined: 01 Jul 2016
Posts: 4
Location: India

PostPosted: Thu Dec 01, 2016 7:33 pm
Reply with quote

I tried the below control card to get the addition part.
Now the problem is that the output I receive is in ZD format and the file is longer than expected.

Control Card:
Code:

//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(1,8,A,28,3,A)                     
  JOINKEYS FILE=F2,FIELDS=(1,8,A,28,3,A)                     
  REFORMAT FIELDS=(F1:1,30,F2:1,30)                           
  OPTION COPY                                                 
  OUTFIL FNAMES=SORTOF01,BUILD=(1,16,17,8,PD,ADD,47,8,PD,25,6)
/*                                                           


Expected output was 30 bytes long same as input file and with the Amount field containing the addition of the Amount fields from File 1 and File 2 for the matched records only.
For unmatched records, the record from the input File 1 is supposed to be as it is copied to output file.
But as you can see below, the output file created is 38 bytes long with the amount field taking up the additional 8 bytes.

Code:

----+----1----+----2----+----3----+---
ABCDANT XYZJE          12349522    HKD
ABCDANT XYZJE          79934401    USD
EFGHK   UVWNDAK        22900302    USD
IJKLAM  RSTKUR          5465503    HKD
MNOPN   OPQANI        876587703    AUD


Note:
- The addition of the packed-decimal values is correct
- The unmatched records are getting skipped from the output.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Dec 01, 2016 8:45 pm
Reply with quote

Use TO=PD and LENGH= to convert the result to PD of the length that you want.
Back to top
View user's profile Send private message
Sushant Garje

New User


Joined: 01 Jul 2016
Posts: 4
Location: India

PostPosted: Fri Dec 02, 2016 1:50 pm
Reply with quote

Thank you Bill,

It worked even without the LENGTH= parameter.
Please find below the control card that worked for my requirement.
Code:

JOINKEYS FILE=F1,FIELDS=(1,8,A,28,3,A)                       
JOINKEYS FILE=F2,FIELDS=(1,8,A,28,3,A)                       
JOIN UNPAIRED F1,F2                                         
REFORMAT FIELDS=(F1:1,30,F2:1,30),FILL=C'$'                 
OPTION COPY                                                 
OUTFIL FNAMES=SORTOF01,INCLUDE=(1,1,CH,NE,C'$',AND,         
                               31,1,CH,NE,C'$'),             
                       BUILD=(1,16,                         
                             17,8,PD,ADD,47,8,PD,TO=PD,     
                             25,6)                           
OUTFIL FNAMES=SORTOF02,INCLUDE=(31,1,CH,EQ,C'$'),BUILD=(1,30)


In above, I used SORTOF01 to get my intended result for matched records with added amount fields.
But I had stated in my first post, I need the unmatched records from the File 1 (driver file) as well.
So, I am using SORTOF02 to get those unmatched records from File 1.
In the next step, I will merge the file from SORTOF01 and SORTOF02 to get the final required file.

I have multiple PD fields in my actual file, for which I will repeat the same logic for other fields in SORTOF01.

Thank You again!
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Dec 02, 2016 2:35 pm
Reply with quote

Well, I'd recommend using the length. It is more typing for now, but it'll save lots of confusion in the long run (people have to look up in a manual how long the field will be while trying to check that the data is in the correct position, a silly thing to leave someone else to do).

Are you using SyncSORT? If not, use the "match marker", ?, for the REFORMAT record, and from F2 only include the data that you need from F2.

Test the match-marker position, and do the calculation only on a match (marker equal to B). Use IFOUTLEN=30 to drop off the excess bytes (match-marker and amount).

If you don't have access to the match-marker, you can work out whether you have a match or not, using a test for the "FILL" character that you have provided.

No need for two OUTFILs. No need for another step.

Also, if you use FILES=01, you don't need FNAMES=SORTOF01. Alternatively, specify a more description name for FNAMES.

Note that the F1,F2 are a "comment" in the JOIN you have shown, plus you get F1 and F2 mismatches anyway from UNPAIRED. F1 we know what you want with mismatches, what about F2 mismatches?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top