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
 
Sort to match 2 files

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

New User


Joined: 05 Apr 2007
Posts: 38
Location: india

PostPosted: Fri May 01, 2009 1:29 am    Post subject: Sort to match 2 files
Reply with quote

Hi ,

I have a requirement to pull both matched & unmatched records from File1 & File2. Both the file1 & file2 have unique key fields with recfm=fb & lrecl=100

File1
Code:

C(15) (1-15)     C(4) (16-19)  C(8) (20-27)
File1-Acct-KEY----File1 Crncy-KEY--File1 Memo-KEY--------

000000252019992   RUP              11502   
000000252019992   RUP              11600   
000000252019992   RUP              11602   



File2

Code:

File2-Acc         File2-crncy    file2-memo
C(15) (1-15)     C(4) (16-19)  C(8) (63-71)
-KEY-----------  -KEY--------  -KEY--------
,********************************* TOP OF D
000000252019992  RUP           11502   
000000252019992  SDL           11700   
000000252019992  USD           11702   


My output should be as mentioned below

Code:

 ----Acc  --    --crncy-  -File1-memo  File2-memo   
000000252019992     RUP    11502       11502  --- < file1 data ----->
000000252019992     RUP    11600       bbbbbb --- < file1 data ----->
00000252019992      RUP    11602       bbbbbb --- < file1 data ----->
000000252019992     SDL    bbbbb       11700   ---< file2 data ----->
000000252019992     USD    bbbbb       11702   ---< file2 data ---->

Please help
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: Fri May 01, 2009 1:41 am    Post subject:
Reply with quote

It's not clear what you're trying to do. Which fields are you matching on? Which fields are you including in the output records? What are the "rules" for getting from input to output?
Back to top
View user's profile Send private message
suyerasi

New User


Joined: 05 Apr 2007
Posts: 38
Location: india

PostPosted: Fri May 01, 2009 2:03 am    Post subject: Reply to: Sort to match 2 files
Reply with quote

Dear Frank,

I am matching both the files (file1 & file2) on 3 key fields (Acc-no, Currency & Memo #). Both the file layouts are same and only difference is that memo number is at differenct offset in both the files
1) when there is a match found on the above keys ,
My first 3 fields in the output should be the 3 key values from file1 & the fourth field (same as 3rd key in file1) should also be popualted with 3rd key (Memo #) from file2. Rest of the fields in the output should have data from file1.

2) When there is no match in file2 for a key in file1

My first 3 fields in the output should be the 3 key values from file1 & the fourth field should be popualted with spaces (no key found in file2) . Rest of the fields in the output should have data from file1.

3) When there is no match in file1 for a key in file2
My first 2 fields in the output should be the 2 key values from file2 & the 3rd output field should be popualted with spaces (no key found in file1) while fourth field should be populated with3rd key from file2 . Rest of the fields in the output should have data from file2.


My output should be like this based on the records in file1 & file2


Code:
<FL1/FL2 ACC-NO><FILE1/FILE2 CURRENCY><FILE1 MEMO#><FILE2 MEMO#><file1/file2 data>


Please let me know if i am clear to you .
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: Fri May 01, 2009 2:16 am    Post subject:
Reply with quote

What is the starting position(s) and length(s) of "file1 data" in input file1?

What is the starting position(s) and length(s) of "file2 data" in input file2?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri May 01, 2009 2:21 am    Post subject: Reply to: Sort to match 2 files
Reply with quote

suyerasi,

create a single line HDR record of 100 bytes to identify which records the file came from and we will use that as an indicator to tag the records using WHEN=GROUP function. Concatenate the files together and since your keys are unique , any matching key will have a sum of 3 and the rest will have 1 or 2 when summed on the group id

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
HDR                                                                 
//         DD *                                                     
000000252019992 RUP 11502                                           
000000252019992 RUP 11600                                           
000000252019992 RUP 11602                                           
//         DD *                                                     
HDR                                                                 
//         DD *                                                     
000000252019992 RUP 11502                                           
000000252019992 SDL 11700                                           
000000252019992 USD 11702                                           
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(101:ID=1))
  SORT FIELDS=(1,27,CH,A),EQUALS                                     
  SUM FIELDS=(101,1,ZD)                                             
  OUTFIL OMIT=(1,3,CH,EQ,C'HDR'),IFOUTLEN=100,                       
  IFTHEN=(WHEN=(101,1,ZD,EQ,1),OVERLAY=(28:8X)),                     
  IFTHEN=(WHEN=(101,1,ZD,EQ,2),OVERLAY=(28:20,8,20:8X)),             
  IFTHEN=(WHEN=(101,1,ZD,EQ,3),OVERLAY=(28:20,8))                   
/*                                                                   
Back to top
View user's profile Send private message
suyerasi

New User


Joined: 05 Apr 2007
Posts: 38
Location: india

PostPosted: Fri May 01, 2009 2:58 am    Post subject: Reply to: Sort to match 2 files
Reply with quote

Dear Kolusu,

Thanks for your reply . I am trying to understand your control statements.

Frank,

File1 data length offset is 28 with 65 bytes in length
File2 data length offset is 28 with 65 byes in length


Regards
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm

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