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
 

 

Match and SUM records

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

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Tue Sep 07, 2010 4:05 pm    Post subject: Match and SUM records
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    Post subject:
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: 785
Location: Chennai, India

PostPosted: Tue Sep 07, 2010 6:32 pm    Post subject: Reply to: Match and SUM records
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: 578
Location: USA

PostPosted: Tue Sep 07, 2010 7:51 pm    Post subject:
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: 785
Location: Chennai, India

PostPosted: Tue Sep 07, 2010 9:50 pm    Post subject: Reply to: Match and SUM records
Reply with quote

No DUPS in both files.
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 Sep 07, 2010 11:26 pm    Post subject:
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: 785
Location: Chennai, India

PostPosted: Wed Sep 08, 2010 3:28 pm    Post subject:
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    Post subject:
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: 578
Location: USA

PostPosted: Wed Sep 08, 2010 5:54 pm    Post subject:
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: 785
Location: Chennai, India

PostPosted: Wed Sep 08, 2010 7:22 pm    Post subject: Reply to: Match and SUM records
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: 578
Location: USA

PostPosted: Wed Sep 08, 2010 10:15 pm    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Sep 17, 2011 2:02 am    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Sep 17, 2011 2:12 am    Post subject:
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    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 1 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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