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 join 2 files

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

New User


Joined: 22 May 2012
Posts: 32
Location: india

PostPosted: Sat May 26, 2012 12:30 pm    Post subject: SORT to join 2 files
Reply with quote

Hi,

I want to join 2 files. The condition is if the key matches then join the files based on the key, but if the record is only present in 1st file and not in 2nd file the output should have record of 1st file and the 2nd file details should be populated as spaces.

for example
File 1 input:

001 NITIN
002 RAHUL
003 ROHIT
004 ABCD

File 2 Input
001 BHARGAVA
002 GUPTA
004 EFGH

The output i am expecting is
001 NITIN BHARGAVA
002 RAHUL GUPTA
003 ROHIT
004 ABCD EFGH

The key is first 3 bytes.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Sat May 26, 2012 2:21 pm    Post subject: Reply to: SORT to join 2 files
Reply with quote

Look at JOIN UNPAIRED,F1 which will get you matches, plus those from file 1 only (confirm exactly in the manual).

In REFORMAT the values from F2 will be space (confirm exactly in the manual).
Back to top
View user's profile Send private message
Nitin Bhargava

New User


Joined: 22 May 2012
Posts: 32
Location: india

PostPosted: Sun May 27, 2012 12:15 am    Post subject: Reply to: SORT to join 2 files
Reply with quote

Thanks Bill.
one more doubt.

I am having a record as
001 NITIN BHARGAVA N 02500 09500 10 A 22 013B
002 RAHUL GUPTA Y 03500 08000 20 A 98 017B

Now for key 001 if the field is 'N' I have to multiply the next two numbers i.e. 02500 and 09500 and replace them by the product. if it is 'Y' as in key 002 the output should be zero. In the same step i have to check if the person code is '10' then add a description as 'OWNER' it can be max 15 bytes. For person code '20' (as in key 002) i have to add a description as 'INSURED'. for status code '22' it is 'ACTIVE' for status '98' it is 'TERMINATED'.

The output will look like
001 NITIN BHARGAVA N 23750000 10 OWNER A 22 ACTIVE 013B
002 RAHUL GUPTA Y 00000000 20 INSURED A 98 TERMINATED 017B
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Sun May 27, 2012 12:26 am    Post subject: Reply to: SORT to join 2 files
Reply with quote

All of that should be OK.

IFTHEN=(WHEN=(start,length,type,EQ,value),do something)

You can MULtiply, and format the output with TO=ZD,LENGTH=len or with an EDIT.

You can use IFTHEN to decode and insert your text values. There is some type of "lookup" available, you'll have to find it in the manual.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue May 29, 2012 10:09 pm    Post subject:
Reply with quote

Nithin Bhargava,

Use the following DFSORT JCL which will give you the desired results
Code:

//STEP0300 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//INA      DD *                                               
----+----1----+----2----+----3----+----4----+----5----+----6--
001 NITIN BHARGAVA N 02500 09500 10 A 22 013B                 
002 RAHUL GUPTA    Y 03500 08000 20 A 98 017B                 
//INB      DD *                                               
001 BHARGAVA                                                 
002 GUPTA                                                     
004 EFGH                                                     
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(1,3,A)                             
  JOINKEYS F2=INB,FIELDS=(1,3,A)                             
  JOIN UNPAIRED,F1                                           
  REFORMAT FIELDS=(F1:1,80)                                   
  INREC IFTHEN=(WHEN=(20,1,CH,EQ,C'Y'),OVERLAY=(22:11C'0')), 
  IFTHEN=(WHEN=(20,1,CH,EQ,C'N'),                             
  OVERLAY=(22:22,5,ZD,MUL,28,5,ZD,M11,LENGTH=11))             
                                                             
  OUTREC BUILD=(1,36,                                         
                34,2,CHANGE=(15,C'10',C'OWNER',               
                                C'20',C'INSURED'),           
                     NOMATCH=(C' '),36,6,                     
                39,2,CHANGE=(20,C'22',C'ACTIVE',             
                                C'98',C'TERMINATED'),         
                     NOMATCH=(C' '),41,40)                   
//*


This would produce the following output

Code:

001 NITIN BHARGAVA N 00023750000 10 OWNER           A 22 ACTIVE               013B
002 RAHUL GUPTA    Y 00000000000 20 INSURED         A 98 TERMINATED           017B
Back to top
View user's profile Send private message
Nitin Bhargava

New User


Joined: 22 May 2012
Posts: 32
Location: india

PostPosted: Mon Jun 04, 2012 12:26 pm    Post subject:
Reply with quote

Thanks SKolusu, it worked.
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