Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Join 2 File using a Join Key

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

New User


Joined: 06 May 2008
Posts: 96
Location: Delhi

PostPosted: Tue Aug 14, 2018 7:36 am    Post subject: Join 2 File using a Join Key
Reply with quote

Can someone help in explaining the logic on how to join 2 files as per the requirements below.

Code:

FILE1  [FB , 80 BYTES]
==================

A234567890.....................HELLO
B234567890.....................HELLO
C434567890.....................HELLO
D254567890.....................HELLO



Code:


FILE2  [FB , 80 BYTES]
==================

A234567890.....................BYE
C234567890.....................BYE
E234567890.....................BYE



I want output like this.

Code:


FILE 3    [FB,80 Bytes]
==================
A234567890.....................HELLO     A234567890.....................BYE
B234567890.....................HELLO     NOT PRESENT
C434567890.....................HELLO     C234567890.....................BYE
D254567890.....................HELLO     NOT PRESENT
NOT PRESENT                                 E234567890.....................BYE

Back to top
View user's profile Send private message

scorp_rahul23

New User


Joined: 06 May 2008
Posts: 96
Location: Delhi

PostPosted: Tue Aug 14, 2018 8:53 am    Post subject: Reply to: Join 2 File using a Join Key
Reply with quote

I tried this below, but this also did not worked.

Code:

//F1ONLY    DD DSN=F9340T.RAMPUP.MERGE.F1ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//F2ONLY    DD DSN=F9340T.RAMPUP.MERGE.F2ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//MERGE     DD DSN=F9340T.RAMPUP.MERGE,                   
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//SYSIN     DD    *                                       
 JOINKEYS FILE=F1,FIELDS=(1,15,A),TYPE=F                   
 JOINKEYS FILE=F2,FIELDS=(1,15,A),TYPE=F                   
  JOIN UNPAIRED,F1,F2                                     
  REFORMAT FIELDS=(?,F1:1,80,F2:1,80)                     
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)       
  OPTION COPY                                             
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1,1,CH,EQ,C'1'),           
    BUILD=(1:2,80,81:C' ',82:C'NOT PRESENT IN TEK',93:61X)
  OUTFIL FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'2'),           
    BUILD=(1:C'NOT PRESENT IN TEK',19:61X,82:82,80)       
  OUTFIL FNAMES=MERGE,INCLUDE=(1,1,CH,EQ,C'B'),           
    BUILD=(1:2,80,81:82,80)   
/*                             
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8787
Location: Welsh Wales

PostPosted: Tue Aug 14, 2018 11:28 am    Post subject:
Reply with quote

Maybe if you show the log and the output we might be better equipped to help you.

Psychic day isn't until Friday this week
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 627
Location: Maryland

PostPosted: Tue Aug 14, 2018 7:49 pm    Post subject: Re: Reply to: Join 2 File using a Join Key
Reply with quote

scorp_rahul23 wrote:
I tried this below, but this also did not worked.

Code:

//F1ONLY    DD DSN=F9340T.RAMPUP.MERGE.F1ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//F2ONLY    DD DSN=F9340T.RAMPUP.MERGE.F2ONLY,             
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//MERGE     DD DSN=F9340T.RAMPUP.MERGE,                   
//             DISP=(NEW,CATLG),UNIT=SYSDA,               
//             SPACE=(TRK,(1,1),RLSE)                     
//SYSIN     DD    *                                       
 JOINKEYS FILE=F1,FIELDS=(1,15,A),TYPE=F                   
 JOINKEYS FILE=F2,FIELDS=(1,15,A),TYPE=F                   
  JOIN UNPAIRED,F1,F2                                     
  REFORMAT FIELDS=(?,F1:1,80,F2:1,80)                     
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)       
  OPTION COPY                                             
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1,1,CH,EQ,C'1'),           
    BUILD=(1:2,80,81:C' ',82:C'NOT PRESENT IN TEK',93:61X)
  OUTFIL FNAMES=F2ONLY,INCLUDE=(1,1,CH,EQ,C'2'),           
    BUILD=(1:C'NOT PRESENT IN TEK',19:61X,82:82,80)       
  OUTFIL FNAMES=MERGE,INCLUDE=(1,1,CH,EQ,C'B'),           
    BUILD=(1:2,80,81:82,80)   
/*                             

As per the example of your desired output you need one single output listing.
In your code example you try to create 3 separate listings.

1) What is the actual requirement?
2) What exactly "did not work(ed)" in your test?
3) For "listing only" purposes it would be much easier to use option FILL=C'-' in your REFORMAT statement, rather than '?' with selection to substitute dummy and senseless words instead of missing pairs.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2309
Location: NY,USA

PostPosted: Tue Aug 14, 2018 8:50 pm    Post subject:
Reply with quote

You were trying to create 3 datasets based on the conditions and probably thought of adding another step to merge them in one data set but you need just one out data set. So understand the below card and this is what you need to do to achieve what you posted.
Code:
//SORT      EXEC  PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTJNF1  DD *
111.................HELLO
222.................HELLO
//SORTJNF2  DD *
111...................BYE
333...................BYE
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
 JOINKEYS FILE=F1,FIELDS=(1,3,A),SORTED
 JOINKEYS FILE=F2,FIELDS=(1,3,A),SORTED
 JOIN UNPAIRED,F1,F2
 REFORMAT FIELDS=(F1:1,25,F2:1,25,?)
 OPTION COPY
 OUTFIL IFTHEN=(WHEN=(51,1,CH,EQ,C'B'),BUILD=(1,25,3X,26,25)),
     IFTHEN=(WHEN=(51,1,CH,EQ,C'1'),BUILD=(1,25,3X,C'NOT PRESENT')),
     IFTHEN=(WHEN=(51,1,CH,EQ,C'2'),BUILD=(C'NOT PRESENT',17X,26,25))

Note: Adjust the offsets per your record layout and rerun.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 627
Location: Maryland

PostPosted: Tue Aug 14, 2018 10:39 pm    Post subject:
Reply with quote

Simplified version with the same final result.
Missing pairs just marked with '-------------------' which is not critical in real life.
Code:
//SORT      EXEC  PGM=SORT
//SYSOUT    DD SYSOUT=*
//SORTJNF1  DD *
111.................HELLO
222.................HELLO
//SORTJNF2  DD *
111...................BYE
333...................BYE
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
 JOINKEYS FILE=F1,FIELDS=(1,3,A),SORTED
 JOINKEYS FILE=F2,FIELDS=(1,3,A),SORTED
 JOIN UNPAIRED,F1,F2
 REFORMAT FIELDS=(F1:1,25,F2:1,25),FILL=C'-'
 OPTION COPY
 OUTREC BUILD=(1,25,3X,26,25)
 END
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2309
Location: NY,USA

PostPosted: Wed Aug 15, 2018 12:13 am    Post subject:
Reply with quote

Quote:
Missing pairs just marked with '-------------------' which is not critical in real life.
I agree. But some people require things to be told clearly and especially dealing with different group and vendors so it’s good to mention what’s missing in words than having some cryptic way.
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 File transfer issue with end of line ... VINAY PALLELA TSO/ISPF 4 Thu Nov 14, 2019 12:01 am
No new posts File compare, where file has header a... VINAY PALLELA SYNCSORT 8 Thu Nov 07, 2019 5:05 pm
No new posts OMIT first and last column of a pipe-... karthikb_itpro DFSORT/ICETOOL 1 Fri Nov 01, 2019 8:24 pm
No new posts Join the keys via JCL abdulrafi DFSORT/ICETOOL 14 Mon Oct 28, 2019 9:45 pm
No new posts How to place name of the file where t... danielgp89 SYNCSORT 18 Thu Oct 17, 2019 12:55 am

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