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
 

 

Report Generation based on different values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sunitha_babu

New User


Joined: 13 Sep 2005
Posts: 6

PostPosted: Mon Mar 14, 2011 3:52 pm    Post subject: Report Generation based on different values
Reply with quote

Hi,

the input file is as below.

0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!P!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!P!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!443!7Z!P!2010-01-04!

0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!R!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!P!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!P!2010-01-04!
0213370000912!2009-11-20!00000845! 0000000000040.60! 0000000000000.00!456!7Z!P!2010-01-04!




The 4th column from last '443' represents the country code(the file has many codes),and 2rd column from last represents either 'O' or 'P' or 'R'

the output should be as below


CCode Date1 Date2 Amt1 Amt2 Status

------ -------- -------- -------- ------- ------
443 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
443 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
443 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
------------------------------------------

TOTALS : 121.80 0.00 3 ---> this represents count


CCode Date1 Date2 Amt1 Amt2 Status
------ -------- -------- -------- ------- ------
456 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
456 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
456 2009-11-20 2010-01-04 0000000000040.60 0000000000000.00 O
------------------------------------------

TOTALS : 121.80 0.00 3 ---> this represents count


Whenever the country code changes,the header and its totals should be generated,this report is only for Status "O",similarly for status "P' and "R".There are many country codes and each has different status (o or p or R)
Back to top
View user's profile Send private message

anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Mon Mar 14, 2011 4:02 pm    Post subject: Reply to: Report Generation based on different values
Reply with quote

Hello,

Please post the file format,length etc information.
Back to top
View user's profile Send private message
sunitha_babu

New User


Joined: 13 Sep 2005
Posts: 6

PostPosted: Mon Mar 14, 2011 4:23 pm    Post subject: Reply to: Report Generation based on different values
Reply with quote

File format is FB,

The length for i/p & o/p columns are the same.
Country Code length : 3
Status Length: 1
Date1 length : 10
Date2 length : 10
Amt 1 length : 17
Amt 2 length : 17
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Mon Mar 14, 2011 6:24 pm    Post subject:
Reply with quote

Hello Sunitha,

Please try the below sort card -

Code:

SORT FIELDS=(71,03,CH,A,78,01,CH,A),EQUALS                           
OUTFIL REMOVECC,BUILD=(71,03,C' ',15,10,C' ',80,10,C' ',36,16,C' ',54,
16,C' ',78,01),                                                       
SECTIONS=(71,03,78,01,                                               
TRAILER3=(1:C'TOTAL : ',TOT=(36,16,SFF,EDIT=(TTTTTTTTTTTTT.TT))))     


Hope this Helps!
Back to top
View user's profile Send private message
sunitha_babu

New User


Joined: 13 Sep 2005
Posts: 6

PostPosted: Mon Mar 14, 2011 9:51 pm    Post subject:
Reply with quote

Hi,

Thanks for the sort card,the amount fields are signed which counts to length 17 as shown below.

0213370000912!2009-11-20!00000845!+0000000000040.60! 0000000000000.00!443!7Z!O!2010-01-04!
0213370000912!2009-11-20!00000845!-0000000000040.60! 0000000000000.00!443!7Z!O!2010-01-04!
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Mar 15, 2011 12:08 am    Post subject: Reply to: Report Generation based on different values
Reply with quote

sunitha_babu,

The following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=Your input file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  SORT FIELDS=(71,3,CH,A,                             $ CCODE     
               78,1,CH,A),EQUALS                      $ STATUS     
                                                                   
  OUTFIL REMOVECC,                                                 
  BUILD=(03:71,03,                                    $ CCODE     
         07:15,10,                                    $ DATE1     
         18:80,10,                                    $ DATE2     
         29:35,17,                                    $ AMT1       
         47:53,17,                                    $ AMT2       
         70:78,01,80:X),                              $ STATUS     
  HEADER2=(01:'CCODE',                                             
           09:'DATE1',                                             
           20:'DATE2',                                             
           33:'AMOUNT1',                                           
           52:'AMOUNT2',                                           
           65:'STATUS',/,                                         
           01:5'=',07:10'=',18:10'=',29:17'=',47:17'=',65:6'='),   
  SECTIONS=(71,3,78,1,   
  TRAILER3=(29:17'=',47:17'=',/,'TOTALS : ',                   
            29:TOT=(35,17,SFF,                                 
                    EDIT=(STTTTTTTTTTTTT.TT),SIGNS=(+,-)),     
            47:TOT=(53,17,SFF,                                 
                    EDIT=(STTTTTTTTTTTTT.TT),SIGNS=(+,-)),/))   
//*


The output from this job is

Code:

CCODE   DATE1      DATE2        AMOUNT1            AMOUNT2      STATUS
===== ========== ========== ================= ================= ======
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
  443 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      O
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      P
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
  456 2009-11-20 2010-01-04  0000000000040.60  0000000000000.00      R
                            ================= =================       
TOTALS :                    +0000000000121.80 +0000000000000.00       
                                                                       
Back to top
View user's profile Send private message
sunitha_babu

New User


Joined: 13 Sep 2005
Posts: 6

PostPosted: Tue Mar 15, 2011 12:31 pm    Post subject: Reply to: Report Generation based on different values
Reply with quote

Its Perfect.Thanks Skolusu.
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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