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
 

 

To convert char to signed numeric, then SUM it

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Apr 15, 2011 3:10 am    Post subject: To convert char to signed numeric, then SUM it
Reply with quote

Hi all,

I have a flat file of FB 436 bytes out of which the amount field starts from 422nd byte and is stored as character format (15bytes).
I want to sum the amount based on Policy number (6 bytes char starting from 19th byte) and Effective date (10 bytes char starting from 39th byte).
I tried to make use of below mentioned SORT card but is abending because amount field is not numeric.

Input (putting the amount field alone from 422nd byte - 436th byte)
Code:
          83.33
          83.33
          -8.09
       26995.00
          14.44
         -13.12
         153.07
         -17.23
         200.98
Output (Need to have the whole file with summed amount from 422nd byte)
For example if the first 2 records have same policy number and effective date, we should get it as

Code:
         166.66
          -8.09
       26995.00
          14.44
         -13.12
         153.07
         -17.23
         200.98

Code:

  SORT FIELDS=(19,6,CH,A,39,10,CH,A)
  SUM FIELDS=(422,15,CH)           


Can anybody please help me in getting the Sort card corrected. I also want to see the resultant amount field in char format (for downloading to excel sheet)

Thanks
Vinu
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 15, 2011 3:44 am    Post subject:
Reply with quote

You can use a DFSORT job like the following to do what you asked for:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/436)
//SORTOUT DD DSN=...  output file (FB/436)
//SYSIN DD *
  SORT FIELDS=(19,6,CH,A,39,10,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    SECTIONS=(19,6,SKIP=0L,
       39,10,TRAILER3=(1,421,
        422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))
/*
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Apr 15, 2011 3:57 am    Post subject:
Reply with quote

Thanks Frank for the code
I have run the SOrt card and got the below mentioned error.

Code:
 SYSIN :                                                                     
    SORT FIELDS=(19,6,CH,A,39,10,CH,A)                                   00150
    OUTFIL REMOVECC,NODETAIL,                                            00180
     SECTIONS=(19,6,SKIP=0L,39,10,                                       00190
     TRAILER3=(1,421,                                                    00191
                 *                                                           
               422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))  00192
 WER903I  SYNCSORT 1.3.2.0 IS NOT LICENSED FOR SERIAL 6D972, TYPE 2097 711, LP
 WER903I  PRODUCT WILL STOP WORKING IN  26 DAYS UNLESS A VALID KEY IS INSTALLE
 WER271A  OUTFIL STATEMENT  : NUMERIC FIELD ERROR                             
 WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                               
 WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                               


The SORT card used by me is
Code:
//STEP02  EXEC PGM=SORT                                               
 //SYSOUT    DD SYSOUT=*                                               
 //SYSPRINT  DD SYSOUT=*                                               
 //SORTIN    DD DSN=<input dsn>,DISP=SHR       
 //SORTOUT   DD DSN=<Output dsn>,                   
 //             DISP=(,CATLG,DELETE),                                 
 //             UNIT=DISK,                                             
 //             SPACE=(CYL,(20,20),RLSE),                             
 //             DCB=(RECFM=FB,LRECL=436,BLKSIZE=0)                     
 //SYSIN     DD *                                                     
    SORT FIELDS=(19,6,CH,A,39,10,CH,A)                                 
    OUTFIL REMOVECC,NODETAIL,                                         
     SECTIONS=(19,6,SKIP=0L,39,10,                                     
     TRAILER3=(1,421,                                                 
               422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))
 /*                                                                   



It is mentioned as numeric erro but could not figure out how to correct it.

Thanks
Vinu
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 15, 2011 4:09 am    Post subject:
Reply with quote

The job works fine with DFSORT. However, the WER messages indicate you're using Syncsort, not DFSORT. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

Please post questions on Syncsort in the JCL Forum, not in the DFSORT Forum.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Apr 15, 2011 5:36 am    Post subject:
Reply with quote

Thanks for the info Frank.
I think I can see ICETOOL in my company.
Whether this requirement can be accomplished using ICETOOL ?

Thanks
Vinu
Back to top
View user's profile Send private message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Fri Apr 15, 2011 5:40 am    Post subject: Reply to: To convert char to signed numeric, then SUM it
Reply with quote

Try it, but I'll bet you find out you have Synctool installed instead.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Apr 15, 2011 7:58 am    Post subject:
Reply with quote

Hi Frank / Kevin,

I have tried using ICETOOL but not getting an answer.
Is it possible to just convert the character format of amount field to just numeric ?

Appreciate your help.

Thanks
Vinu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1714
Location: UK

PostPosted: Fri Apr 15, 2011 10:01 am    Post subject:
Reply with quote

Vinu - you have been told that you are using SYNCSORT so why do you keep posting in the DFSORT section? SYNCSORT nd DFSORT are NOT the same product and their control cards are not necessarily 100% compatible.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Apr 15, 2011 12:42 pm    Post subject:
Reply with quote

vinu78 wrote:
I have tried using ICETOOL but not getting an answer.
Did you mean - in the above Job, you've used PGM=ICETOOL instead of PGM=SORT?

You get, WER271A, NUMERIC FIELD ERROR, which means a numeric field has been improperly specified on the indicated SyncSort control statement. See, if the position you 've used in Trailer3 actually have the numeric fields in those positions...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 15, 2011 9:50 pm    Post subject:
Reply with quote

Quote:
Thanks for the info Frank.
I think I can see ICETOOL in my company.
Whether this requirement can be accomplished using ICETOOL ?


Since you have Syncsort installed, you would be using Syncsort's SYNCTOOL, not DFSORT's ICETOOL. But whether you're using PGM=SORT or PGM=ICETOOL the solution would essentially be the same.

The root of your problem is that DFSORT supports large trailer fields, but Syncsort does not. I'll leave it to others to tell you how to get around that Syncsort limitation.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Apr 19, 2011 8:22 pm    Post subject:
Reply with quote

Thanks Frank for the information.
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
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 Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am


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