Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Add PD field from 2nd file to PD in 1st
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Thu Dec 01, 2016 5:38 pm    Post subject: Reply to: Add PD field from 2nd file to PD in 1st
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    Post subject: Reply to: Add PD field from 2nd file to PD in 1st
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Thu Dec 01, 2016 8:45 pm    Post subject: Reply to: Add PD field from 2nd file to PD in 1st
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    Post subject: Reply to: Add PD field from 2nd file to PD in 1st
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Dec 02, 2016 2:35 pm    Post subject: Reply to: Add PD field from 2nd file to PD in 1st
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 22 Tue Dec 06, 2016 8:04 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us