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

Match and SUM records
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Tue Sep 07, 2010 4:05 pm
 Reply with quote
Hi all,

Input1: FB, 16 bytes, No Dups; 1 to 8 bytes is key; the remaining is PD value.

Code:
1234567800000010(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
2222222200000345(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
5555555500000000(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
8888888800000150(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
8888888900000050(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)

Input2: FB, 16 bytes, No Dups; 1 to 8 bytes is key; the remaining is PD value.

Code:
1234567800000020(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
2222222200000300(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
3333333300000300(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
5555555500000010(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
6666660660000012(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
8888888800000150(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
9999999900000050(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)

Expected output: FB, 16 bytes, No Dups; 1 to 8 bytes is key; the remaining is PD value.

Code:
1234567800000030(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
2222222200000645(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
5555555500000010(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
8888888800000300(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)
8888888900000050(It is PD field - 9 to 16 bytes, Length - 8 bytes - I showed it as ZD here)

I need to get all the records from Input1 to output. If a record is matched, SUM value of 8 to 16 bytes of those records should be in output.

I coded below SORT steps to do the same.
Code:
//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=INPUT1
//SORTOUT  DD DSN=OUT1  --> FB, 17 BYTES 
//SYSIN    DD *                                                 
   INREC BUILD=(1,16,C'1')
/*
//*
//STEP0200 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=INPUT2
//SORTOUT  DD DSN=OUT2  --> FB, 17 BYTES 
//SYSIN    DD *                                                 
   INREC BUILD=(1,16,C'2')
/*
//*
//STEP0300 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=OUT1
//         DD DSN=OUT2
//SORTOUT  DD DSN=OUTPUT  --> FB, 16 BYTES 
//SYSIN    DD *                                                 
  SORT FIELDS=(1,8,CH,A),EQUALS                                 
  SUM FIELDS=(9,8,PD)                                           
  OUTFIL BUILD=(1,16),INCLUDE=(17,1,ZD,EQ,1)
/*
//*


BTW, ICETOOL is in my installation with ICE201I F RECORD TYPE ...

Will these work as expected? I can't use SPLICE of ICETOOL since my JCL standards won't allow using of DISP=MOD and temp data sets.

Please suggest.

Thank you!
Back to top
View user's profile Send private message
Prasun De

New User


Joined: 17 Jan 2008
Posts: 28
Location: Kolkata, INDIA
PostPosted: Tue Sep 07, 2010 5:53 pm
 Reply with quote
Aren't you getting the expected results?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Tue Sep 07, 2010 6:32 pm
 Reply with quote
Needs to be tested. I don't have access to my system just now.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Tue Sep 07, 2010 7:51 pm
 Reply with quote
Gnana Sekaran Nallathambi,
Could you have duplicates in File1 and/or File2 for the keys (1-8 bytes)?

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

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Tue Sep 07, 2010 9:50 pm
 Reply with quote
No DUPS in both files.
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: Tue Sep 07, 2010 11:26 pm
 Reply with quote
Quote:
I meant I havn't tested it as no access to system right now.


So wait until you have access and test it. Why do you need somebody else to test it for you and figure out if it works before you can?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Wed Sep 08, 2010 3:28 pm
 Reply with quote
Hi Frank,

I tested the Code and got the expected output.

I hope I'm correct with the code. Please let me know if I'm wrong.

Thank you. Have a good day!
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune
PostPosted: Wed Sep 08, 2010 5:06 pm
 Reply with quote
Quote:
I hope I'm correct with the code. Please let me know if I'm wrong.


if you are satisfied, then we find nothing wrong in code icon_biggrin.gif
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Sep 08, 2010 5:54 pm
 Reply with quote
Gnana Sekaran Nallathambi,
Here is a one pass approach. See if this works for you...

Code:

//STEP01   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
//SORTOUT  DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)         
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  INREC BUILD=(80X)                                                 
  OUTFIL REMOVECC,NODETAIL,HEADER1=(3C'$',9:X'000000000000000C')     
//*                                                                 
//STEP02   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP01.SORTOUT           
//         DD DISP=SHR,DSN=YOUR INPUT FILE1                         
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP01.SORTOUT           
//         DD DISP=SHR,DSN=YOUR INPUT FILE2                         
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(17:ID=1)) 
  SORT FIELDS=(1,8,CH,A),EQUALS                                     
  SUM FIELDS=(9,8,PD,17,1,ZD)                                       
  OUTFIL INCLUDE=(17,1,SS,EQ,C'1,3',AND,1,3,CH,NE,C'$$$'),           
         BUILD=(1,8,9,8)                         
//*                                                                 


In your original solution(step0300) if you use OUTFIL BUILD=(1,16),INCLUDE=(17,1,ZD,EQ,1), you will end up selecting unique records from FILE1. In your expected output, you show for common keys in both files, you want to sum the PD amounts for which 17th position will have value of 3 and not 1. Based on this understading(not sure if correct!!!), you may want to change your include condition in step0300 to INCLUDE=(17,1,zd,eq,1,or,17,1,zd,eq,3).

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

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Wed Sep 08, 2010 7:22 pm
 Reply with quote
Hi sqlcode1,

Thanks for the reply. As such, I can't use your code as usage of temp data sets are not allowed here. I'll try with making HDR data set as cataloged.

In STEP0300 step, SUM fields is not on 17th byte. So, I use INCLUDE as 17,1,ZD,EQ,1

I need to get all the records from Input1 to output. If a record is matched, SUM value of 8 to 16 bytes of those records should be in output. Non-matched record from Input1 should come as such in output. Non-matched record from Input2 is not needed in output.

Am I missing anything here?

Thank you!
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Sep 08, 2010 10:15 pm
 Reply with quote
Gnana Sekaran Nallathambi,

Quote:
In STEP0300 step, SUM fields is not on 17th byte. So, I use INCLUDE as 17,1,ZD,EQ,1
Yes, you are right here.

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

New User


Joined: 20 Nov 2007
Posts: 37
Location: USA
PostPosted: Fri Sep 16, 2011 3:05 pm
 Reply with quote
Use simple sort with below condition.

//STEP0300 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=OUT1
// DD DSN=OUT2
//SORTOUT DD DSN=OUTPUT --> FB, 16 BYTES
//SYSIN DD *
SORT FIELDS=(1,8,CH,A),
SUM FIELDS=(9,8,PD)
OUTREC (1:1,8,:9,8)
//*
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 Sep 17, 2011 2:02 am
 Reply with quote
Santy,

Your "solution" is syntactically and functionally incorrect. Please don't post solutions you haven't actually tested and verified against the expected results.
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 Sep 17, 2011 2:12 am
 Reply with quote
Gnana,

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

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=INPUT1
//IN2 DD DSN=INPUT2
//SORTOUT DD DSN=OUTPUT
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,8,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,8,A),SORTED
  REFORMAT FIELDS=(F1:1,16,F2:9,8,?)
  JOIN UNPAIRED,F1
  OPTION COPY
  INREC IFTHEN=(WHEN=(25,1,CH,EQ,C'B'),
     BUILD=(1,8,9,8,PD,ADD,17,8,PD,TO=PD,LENGTH=8))
/*
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 -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
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 Join multiple records using splice DFSORT/ICETOOL 5
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