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
 

 

sorting and retaining the first record.

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

New User


Joined: 07 Mar 2006
Posts: 16

PostPosted: Tue Feb 20, 2007 12:57 pm    Post subject: sorting and retaining the first record.
Reply with quote

Hi,

I have a record of the following layout:

Code:
name                  date             amount
nayankumar         01012007          12000
nikhilkumar        02022007          13000
nayankumar         01022007          12000
nikhilkumar        02152007          13000
nayankumar         02152007          12000


i want to sort these records based on the name and date field .Also i want to sum the amounts which are having the same names.

The output should be :
Code:
name                  date             amount
nayankumar          01012007        36000
nikhilkumar         02022007        26000

Here , if you see , i have to retain the date which is smallest and also i have to sum the amounts based on the names.

Can anyone give me the solution for this?How to do it using JCL.
Back to top
View user's profile Send private message

reddy_prn

New User


Joined: 15 Feb 2007
Posts: 8
Location: India

PostPosted: Tue Feb 20, 2007 5:45 pm    Post subject: Re: sorting and retaining the first record.
Reply with quote

Please find attched my test program.
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: Tue Feb 20, 2007 9:24 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
* Sort on name, yyyy and mmdd.
  SORT FIELDS=(1,15,CH,A,24,4,CH,A,20,4,CH,A)
/*
//CTL2CNTL DD *
  OPTION EQUALS,ZDPRINT
* Sort on name.
  SORT FIELDS=(1,15,CH,A)
* Sum amount.
  SUM FIELDS=(38,5,ZD)
/*
Back to top
View user's profile Send private message
reddy_prn

New User


Joined: 15 Feb 2007
Posts: 8
Location: India

PostPosted: Wed Feb 21, 2007 3:41 pm    Post subject: Re: sorting and retaining the first record.
Reply with quote

Hi Frank,

When my input is :
name date amount
nayankumar 20070101 99000
nikhilkumar 20070202 40000
nayankumar 20070102 89000
nikhilkumar 20070215 30000
nayankumar 20070215 88000

Output is being displayed as:

nayankumar 20070101 99000
nayankumar 20070102 89000
nayankumar 20070215 88000
nikhilkumar 20070202 70000

Please let me know how to solve this issue. Thank You.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Feb 21, 2007 4:05 pm    Post subject:
Reply with quote

Hi There,

Code:
//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
* Sort on name, yyyy and mmdd.
  SORT FIELDS=(1,15,CH,A,24,4,CH,A,20,4,CH,A)
  SUM FIELDS=(38,5,ZD) 
/*
//CTL2CNTL DD *
  OPTION EQUALS,ZDPRINT
* Sort on name.
  SORT FIELDS=(1,15,CH,A)
* delete duplicate
  SUM FIELDS=NONE
/*


Try this & let us know the result
Back to top
View user's profile Send private message
reddy_prn

New User


Joined: 15 Feb 2007
Posts: 8
Location: India

PostPosted: Wed Feb 21, 2007 6:33 pm    Post subject: Re: sorting and retaining the first record.
Reply with quote

The code is not working as intended.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Feb 21, 2007 6:54 pm    Post subject: Re: sorting and retaining the first record.
Reply with quote

reddy_prn wrote:
The code is not working as intended.
Post your code! icon_rolleyes.gif
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: Wed Feb 21, 2007 10:36 pm    Post subject:
Reply with quote

reddy,

The DFSORT job I gave was based on the original poster's input and works with that input. There are two critical differences between your input and the original posters input that prevent the original job from working with your input.

First, the original date was in the form mmddyyyy whereas your date is in the form yyyymmdd. Second, the sum for the amounts in the original input did not exceed 5 digits whereas yours do exceed 5 digits. If you look at your DFSMSGS output, you'll see:

ICE152I 0 OVERFLOW DURING SUMMATION - RC=0

This indicates that your sum values overflowed so the records could not be summed.

Here's a revised DFSORT/ICETOOL job that works with your different input:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
* Sort on name and yyyymmdd.
  SORT FIELDS=(1,15,CH,A,24,4,CH,A,20,4,CH,A)
/*
//CTL2CNTL DD *
  OPTION EQUALS,ZDPRINT
* Sort on name.
  SORT FIELDS=(1,15,CH,A)
* Sum on 6-byte amount to prevent overflow.
  SUM FIELDS=(37,6,ZD)
/*
Back to top
View user's profile Send private message
reddy_prn

New User


Joined: 15 Feb 2007
Posts: 8
Location: India

PostPosted: Thu Feb 22, 2007 10:36 am    Post subject: Re: sorting and retaining the first record.
Reply with quote

Hi Frank,

Thank You for the reply.
If I give leading zeros to my input sum fields, then the program is able to display the sum in 6-Byte Field. If I don't give leading zeros in my input sum fields, then S0C7 abend is happening.
But the same problem is happening when I change my input sum fields.

When my input is :
name date amount
nayankumar 20070101 999000
nikhilkumar 20070202 440000
nayankumar 20070102 889000
nikhilkumar 20070215 330000
nayankumar 20070215 888000

Output is being displayed as:

nayankumar 20070101 999000
nayankumar 20070102 889000
nayankumar 20070215 888000
nikhilkumar 20070202 770000
Back to top
View user's profile Send private message
nitinkhobragade1

New User


Joined: 07 Mar 2006
Posts: 16

PostPosted: Thu Feb 22, 2007 12:46 pm    Post subject: Re: sorting and retaining the first record.
Reply with quote

Thanks to Frank,reddy and guptae for the quick response,but my issue is not yet resolved.The reason behind that is the amount field having comma( ,) as below:

2,222.00
33,33,33,333.00

I am recieving the amount fields in the char format
so my records are in the following format:
name date amount
nayankumar 01012007 12,000.00
nikhilkumar 02022007 13,000.00
nayankumar 01022007 12,000.00
nikhilkumar 02152007 13,000.00
nayankumar 02152007 12,000.00

the first control card
SORT FIELDS=(1,15,CH,A,24,4,CH,A,20,4,CH,A) works well.


but when i use the second control card:
SORT FIELDS=(1,15,CH,A)
SUM FIELDS=(38,5,ZD) ........it abends with S0C7 error.

i also tried using SUM FIELDS=(38,5,CH)...but its giving me jcl error....and i guess it wont work as well.


Can anyone of you suggest me what do i need to do in this case?
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: Thu Feb 22, 2007 9:37 pm    Post subject:
Reply with quote

reddy,

It's difficult to hit a moving target. I don't know if you got the 0C7s with just blanks and digits (you wouldn't with DFSORT) or with commas and decimal points (you would).

At any rate, you can't use ZD format for values with commas or decimal points. You have to use UFF format to convert the values to ZD values, SUM them and then reformat them back to their original form. Here's a revised DFSORT/ICETOOL job. I assumed your input file has RECFM=FB and LRECL=80, but you can change the job appropriately for other attributes.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD *
nayankumar         01012007          12,000.00
nikhilkumar        02022007          13,000.00
nayankumar         01022007          12,000.00
nikhilkumar        02152007          13,000.00
nayankumar         02152007          12,000.00
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,15,CH,A,24,4,CH,A,20,4,CH,A)
/*
//CTL2CNTL DD *
  OPTION EQUALS,ZDPRINT
  INREC OVERLAY=(81:38,9,UFF,TO=ZD,LENGTH=7)
  SORT FIELDS=(1,15,CH,A)
  SUM FIELDS=(81,7,ZD)
  OUTREC BUILD=(1,37,38:81,7,ZD,EDIT=(TT,TTT.TT),47,34)
/*


In the future, please try to give all of the relevant details in your first post to save time.
Back to top
View user's profile Send private message
reddy_prn

New User


Joined: 15 Feb 2007
Posts: 8
Location: India

PostPosted: Fri Feb 23, 2007 11:13 am    Post subject: Re: sorting and retaining the first record.
Reply with quote

Hi Frank.
I will try to post with as many as details as possible from now onwards.

I have posted a report creation problem on 16-Feb-07. Please let me know if there is a DFSORT solution for that problem.

Thank You.
Back to top
View user's profile Send private message
nitinkhobragade1

New User


Joined: 07 Mar 2006
Posts: 16

PostPosted: Fri Feb 23, 2007 12:40 pm    Post subject:
Reply with quote

Thanks to Frank and reddy .

The code is working well !!
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 Feb 23, 2007 9:29 pm    Post subject:
Reply with quote

Quote:
I have posted a report creation problem on 16-Feb-07. Please let me know if there is a DFSORT solution for that problem.


I don't see that post. What is the URL?
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 Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm


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