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
 

 

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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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