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

To get the count of the records based on a conditions
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Fri Nov 11, 2011 6:28 pm
 Reply with quote
Hello,

I have a huge file with cardnumber(5), and a foreign/domestic transaction indicator(1). The file looks as below:

A1234F
A1234D
A2345D
A1234F
A2345D
A2345F
A3456F

Requirement is to get count for each cardnumber based on foreign/domestic transaction

Output should like below:

CM# Foreign count Domestic count
A1234 2 1
A2345 1 2
A3456 1 0

I have sorted the file based on the Foreign or domestic indicator and next I got the count of each file in two different files. So now am confused how to get the concatenate the files and get the output in one file

I have used the below sort card to pull the counts of each CM#

Code:

  SORT FIELDS=(1,5,CH,A)                                 
  OUTFIL REMOVECC,NODETAIL,                               
  SECTIONS=(1,5,TRAILER3=(1,5,COUNT=(M10,LENGTH=19)))   
/*                                                         


Please help me out, if I could do it in a single sort ? or if there is any way to concatenate the file get the required output

Thanks & Regards,
RK
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 Nov 11, 2011 7:23 pm
 Reply with quote
How about a build for the file, which two fields for counters which you set to one and zero appropriately.

Then sort on the card number.

Then SUM on the card number and the two new fields.

That should all be in one sort step.
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Fri Nov 11, 2011 8:05 pm
 Reply with quote
Its little confusing.. can you help me with a sample code ?

Thanks in advance.
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 Nov 11, 2011 8:14 pm
 Reply with quote
It shouldn't be too confusing.

You can access the data before the sort is performed.

You do the sort.

You do the SUM.

There are many good examples in the DFSORT forum. The manuals are available on the web.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Nov 11, 2011 8:35 pm
 Reply with quote
ratnakumar,
See if below helps. I am assuming input is 80 byte FB.

Code:

//STEP0001 EXEC PGM=SORT                                             
//SORTIN   DD  *                                                     
A1234F                                                               
A1234D                                                               
A2345D                                                               
A1234F                                                               
A2345D                                                               
A2345F                                                               
A3456F                                                               
//SORTOUT  DD  SYSOUT=*                                             
//SYSIN DD *                                                         
 INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:7C'0',C'1')),       
       IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(89:7C'0',C'1'))       
 SORT FIELDS=(1,5,ZD,A),EQUALS                                       
 OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                               
        HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT',80:X),
        SECTIONS=(1,5,                                               
                  TRAILER3=(1,5,6X,TOT=(81,8,ZD,M10,LENGTH=8),       
                                8X,TOT=(89,8,ZD,M10,LENGTH=8),80:X))
/*                                                                   
//SYSOUT DD SYSOUT=*                                                 
//*                                                                 

OUTPUT
Code:
CM#   FOREIGN COUNT  DOMESTIC COUNT
A1234             2               1
A2345             1               2
A3456             1               0

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

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Sat Nov 12, 2011 12:11 am
 Reply with quote
Here's a slightly improved version of sqlcode1's job. Note that you only need 1 for the totals, not 00000001. Note also that you don't need 80:X in the HEADERx or TRAILERx operands - BUILD=(80X) takes care of setting the record length.

Code:

//STEP0001 EXEC PGM=SORT                                           
//SORTIN   DD  *                                                   
A1234F                                                             
A1234D                                                             
A2345D                                                             
A1234F                                                             
A2345D                                                             
A2345F                                                             
A3456F                                                             
//SORTOUT  DD  SYSOUT=*                                           
//SYSIN DD *                                                       
 INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),           
       IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))           
 SORT FIELDS=(1,5,ZD,A),EQUALS                                     
 OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                             
        HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),   
        SECTIONS=(1,5,                                             
                  TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),     
                                8X,TOT=(82,1,ZD,M10,LENGTH=8)))   
/*                                                                 
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Sat Nov 12, 2011 12:23 am
 Reply with quote
Frank,
Thanks for the correction.

36_8_11.gif

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

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Mon Nov 14, 2011 2:13 pm
 Reply with quote
Thank you sqlcode1 & Frank for posting it.

I tried the above code, but it failed with SOC 0C7.

+WER999A jobname,stepname, - UNSUCCESSFUL SORT 0C7 S
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy
PostPosted: Mon Nov 14, 2011 2:19 pm
 Reply with quote
the WER messages indicate that You are using SYNCSORT

Frank is a DFSORT developer,
since SYNCSORT and DFSORT are competitive products He will not answer SYNCSORT related questions

topic moved to the JCL forum ( the one appropriate for SYNCSORT issues )
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Mon Nov 14, 2011 8:27 pm
 Reply with quote
ratnakumar,
I don't have Syncsort or its documentation but I believe all the functions used in the sort above should be available in Syncsort as well.

If you don't mind, could you post your entire error message?

Thanks,
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon Nov 14, 2011 9:39 pm
 Reply with quote
Hello,

If the process raises an 0c7 abend it is because of an invalid numeric value.

Two reasons this could be caused are corrupt data or incorrect data positions in the sort control statements.

As requested, you need to post (all of) the diagnostic information generated by the run.
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: Tue Nov 15, 2011 12:25 am
 Reply with quote
Would a code which was not F or D on your input give you this problem?
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Tue Nov 15, 2011 1:58 pm
 Reply with quote
Here is the complete information in JESMSGLOG

Code:
+WER999A jobname,STEP01A ,        -  UNSUCCESSFUL SORT 0C7 S
+AB400A      ===== ABEND-AID =====  751                     
   REPORT WRITTEN TO: SYS4.ABENDAID.REPORT.DIRECTRY         
   REPORT NUMBER: 0355491                                   
   JOB-jobname STEP-STEP01A                                 
   ABEND CODE=S0C7                                           
     ACCESS YOUR ABEND-AID REPORTS BY EXECUTING             
     AA FROM THE ISPF PRIMARY OPTION MENU.                   
IEA995I SYMPTOM DUMP OUTPUT  982                             
SYSTEM COMPLETION CODE=0C7  REASON CODE=00000000             
 TIME=02.31.45  SEQ=49489  CPU=0000  ASID=0103               
 PSW AT TIME OF ERROR  078D1000   80019582  ILC 4  INTC 07   
   NO ACTIVE MODULE FOUND                                   
   NAME=UNKNOWN                                             
   DATA AT PSW  0001957C - 000F4430  A9E04710  A9A69120     
   AR/GR 0: 00000000/00000000   1: 00000000/0001B970         
         2: 00000000/00000051   3: 00000000/0000000F         
         4: 00000000/00000008   5: 00000000/0001C698         
         6: 00000000/00023D70   7: 00000000/00023E00         
         8: 00000000/00023F18   9: 00000000/00000000         
         A: 00000000/00018C4A   B: 00000000/00017C4A         
         C: 00000000/0001C698   D: 00000000/0000C5CC         

Information in the ABENDAID:

A data exception, S0C7, occurred during execution of a program in
module SS13X02C.
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: Tue Nov 15, 2011 2:09 pm
 Reply with quote
We need the sort cards you used.

Are you getting a dump? We don't need that, but you could look to see if there is such a thing as a "current record" or similar.

EDIT: Also all the WER messages please.
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Tue Nov 15, 2011 2:15 pm
 Reply with quote
@ Bill..

sort card :

Code:
 INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),       
       IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))         
 SORT FIELDS=(1,5,ZD,A),EQUALS                                 
 OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                         
        HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),
        SECTIONS=(1,5,                                         
                  TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8), 
                                8X,TOT=(82,1,ZD,M10,LENGTH=8)))

I checked for current , last record but could not get any information. One interesting line I found was mentioned below:

Code:
*******************************************                         
*           Call Trace Summary            *                         
*******************************************                         
                                                                     
*******Calling*******  ****Return****           *******Called********
Load-Mod  Program      Type     Value           Program      Load-Mod
                                                                     
*SYSTEM                                Links to QKQMAIN      SYNCSORT


The program causing the error is not on the calling
chain.

Thank you..
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy
PostPosted: Tue Nov 15, 2011 2:23 pm
 Reply with quote
might be SYNCSORT is picky about lengths and true numerics
try this one


Code:
 ****** ***************************** Top of Data ******************************
 000001 //ENRICO1  JOB NOTIFY=&SYSUID,                                         
 000002 //             MSGLEVEL=(1,1),CLASS=A,MSGCLASS=H                       
 000003 //*                                                                     
 000004 //ICE     EXEC PGM=SORT                                                 
 000005 //SYSOUT    DD SYSOUT=*                                                 
 000006 //SYSPRINT  DD SYSOUT=*                                                 
 000007 //SORTIN    DD *                                                       
 000008 A1234F                                                                 
 000009 A1234D                                                                 
 000010 A2345D                                                                 
 000011 A3456F                                                                 
 000012 //SORTOUT   DD SYSOUT=*                                                 
 000013 //SYSIN     DD *                                                       
 000014   INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'10')),             
 000015         IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(81:C'01'))               
 000016   SORT  FIELDS=(1,5,CH,A),EQUALS                                       
 000017   OUTFILE REMOVECC,NODETAIL,BUILD=(80X),                               
 000018         HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),         
 000019         SECTIONS=(1,5,                                                 
 000020                   TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),         
 000021                                 8X,TOT=(82,1,ZD,M10,LENGTH=8)))         
 000022 //                                                                     
 ****** **************************** Bottom of Data ****************************


result
Code:
********************************* TOP OF DATA **********************************
CM#   FOREIGN COUNT  DOMESTIC COUNT                                             
A1234             1               1                                             
A2345             0               1                                             
A3456             1               0                                             
******************************** BOTTOM OF DATA ********************************



notice the difference from the previous examples
the overlay is for 2 chars to avoid a short record for the F case and to provide a proper numeric for a ZD
the sort statement has been changed from ZD to CH

tested with dfsort to see that the correct results are still achieved

and ...
use the code tags to make things more readable for people willing to help
Your previous posts were edited to provide them
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: Tue Nov 15, 2011 2:37 pm
 Reply with quote
ratnakumar wrote:
[...] One interesting line I found was mentioned below:

*******************************************
* Call Trace Summary *
*******************************************

*******Calling******* ****Return**** *******Called********
Load-Mod Program Type Value Program Load-Mod

*SYSTEM Links to QKQMAIN SYNCSORT

The program causing the error is not on the calling
chain.

[...]


Well spotted. Good to seem someone who can post stuff for us which seems "unusual".
However, since it is a S0C7 I doubt it is of significance in this instance. I suspect that Syncsort has its own calling conventions, which Abend-Aid is unaware of. Without the source, there's not much we can do with the information if it was there :-)

I think go with enrico's code and see what happens. If that fails, check that there are only D and F codes on your data.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy
PostPosted: Tue Nov 15, 2011 2:43 pm
 Reply with quote
anyway
Quote:
0C7
Data exception. The reason code is whatever the data exception code is in field SDWADXC in the SDWA (systems diagnostic work area).


the data is not what You think icon_cool.gif
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Tue Nov 15, 2011 3:23 pm
 Reply with quote
Yes... it works on Syncsort...
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy
PostPosted: Tue Nov 15, 2011 3:27 pm
 Reply with quote
Quote:
Yes... it works on Syncsort...


which one of the snippets posted icon_question.gif
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Tue Nov 15, 2011 5:09 pm
 Reply with quote
ok.. let me check
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Tue Nov 15, 2011 5:18 pm
 Reply with quote
It worked.. Thanks you guys..
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: Tue Nov 15, 2011 5:49 pm
 Reply with quote
Thanks for letting us know. Can you confirm that it was enrico's code you used? May help others with similar problems.
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore
PostPosted: Tue Nov 15, 2011 7:03 pm
 Reply with quote
Yes, I used enrico's code. and it worked successfully
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy
PostPosted: Tue Nov 15, 2011 7:12 pm
 Reply with quote
well, it would be useful if some good soul could check about the pickiness of SYNCSORT

I tested with dsfort also for records not matching the F or the D and I got 0 for the totals

can anybody please confirm that running

Code:
 ****** ***************************** Top of Data ******************************
 000001 //ENRICO1  JOB NOTIFY=&SYSUID,                                         
 000002 //             MSGLEVEL=(1,1),CLASS=A,MSGCLASS=H                       
 000003 //*                                                                     
 000004 //ICE     EXEC PGM=SORT                                                 
 000005 //SYSOUT    DD SYSOUT=*                                                 
 000006 //SYSPRINT  DD SYSOUT=*                                                 
 000007 //SORTIN    DD *                                                       
 000008 A1234F                                                                 
 000009 A1234D                                                                 
 000010 A2345D                                                                 
 000011 A3456F                                                                 
 000012 Z0000                                                                   
 000013 //SORTOUT   DD SYSOUT=*                                                 
 000014 //SYSIN     DD *                                                       
 000015   INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),               
 000016         IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))               
 000017   SORT  FIELDS=(1,5,ZD,A),EQUALS                                       
 000018   OUTFILE REMOVECC,NODETAIL,BUILD=(80X),                               
 000019         HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),         
 000020         SECTIONS=(1,5,                                                 
 000021                   TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),         
 000022                                 8X,TOT=(82,1,ZD,M10,LENGTH=8)))         
 000023 //                                                                     
 ****** **************************** Bottom of Data ****************************


SYNCSORT will abend

while DFSORT will happily digest everything
Code:
********************************* TOP OF DATA **********************************
CM#   FOREIGN COUNT  DOMESTIC COUNT                                             
A1234             1               1                                             
A2345             0               1                                             
A3456             1               0                                             
Z0000             0               0                                             
******************************** BOTTOM OF DATA ********************************
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 -> JCL & VSAM Goto page 1, 2  Next

 

Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts How to load to DB2 with column level ... DB2 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us