Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need help on SUM statement and eliminating duplicates

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

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Jun 14, 2010 1:45 pm    Post subject: Need help on SUM statement and eliminating duplicates
Reply with quote

Hi All,

I have a wired requirement. My input file is Below

ACC TYPE
1 A
1 B
1 C
2 A
2 B
2 C
3 D
3 E

I want to sort this file on Account with eliminating duplicates But in this case I want to have preference for TYPE= A, means the record which will be written after eliminating duplicates will have preference for Type A.
if there is no records for particular account with type as A then any type can be written but only one row.

So in this case my out put needs to be

ACCOUNT TYPE
1 A
2 A
3 D

I agree this can be done with cobol program but is there any way this can be achieved using DFSORT

Thanks in Advance

Regards,
Chandan
Back to top
View user's profile Send private message

guptae

Moderator


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

PostPosted: Mon Jun 14, 2010 1:57 pm    Post subject:
Reply with quote

Hello Chandan,

Please use below sort card:

Code:
SORT FIELDS=(1,1,CH,A,3,1,CH,A)
SUM FIELDS=NONE         
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Jun 14, 2010 2:11 pm    Post subject:
Reply with quote

Hi Ekta,

Thanks for your reply..

Sorry to say I was aware of this solution..and the type code which have mentioned if for the example purpose..

its not always true the required record will come first in sort order

For given example the type code is A so this solution will work but type code can be changed to 'B' or 'C'

Please let me know if I am missing anything

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

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 14, 2010 7:17 pm    Post subject:
Reply with quote

Hello,

Show a more complete set of input data and the output you want from this expanded input. Clearly explain what "For given example the type code is A so this solution will work but type code can be changed to 'B' or 'C' " means. It is not at all clear.

Possibly this will be easier to understand if you use more realistic values rather than alphabet soup. . .

When posting the data, use the Code tag and Preview your post to make sure it looks like you want.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon Jun 14, 2010 9:21 pm    Post subject:
Reply with quote

chandan.inst,

Assuming your input is FB recfm and 80 byte LRECL. use the following DFSORT/ICETOOL JCL which will give you the desired results. Depending on your type code you want to come first, change the 2nd IFTHEN statement to reflect the type code you interested.

Code:

//STEP0100 EXEC PGM=ICETOOL                               
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN       DD *                                           
1 A                                                       
1 B                                                       
1 C                                                       
2 A                                                       
2 B                                                       
2 C                                                       
3 D                                                       
3 E                                                       
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  SELECT FROM(IN) TO(OUT) ON(1,1,CH) FIRST USING(CTL1)     
//CTL1CNTL DD *                                           
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:C'1')),             
  IFTHEN=(WHEN=(3,1,CH,EQ,C'A'),OVERLAY=(81:C'0'))         
  SORT FIELDS=(1,1,CH,A,81,1,CH,A)                         
  OUTFIL FNAMES=OUT,BUILD=(1,80)                           
//*
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Jun 15, 2010 10:34 am    Post subject:
Reply with quote

Thanks Kolusu and Dick for your help

Dick,
I will surely keep in mind point mentioned by you henceforth..

Regards,
Chandan
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 Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us