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
 
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: 95
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: 95
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: 8720
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 User


Joined: 29 Apr 2008
Posts: 365
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: 2030
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 User


Joined: 29 Apr 2008
Posts: 365
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: 2030
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 Picking up the latest PS file pema_yozer JCL & VSAM 6 Thu Oct 11, 2018 12:26 pm
No new posts Process only last key record in a fil... Naveen.katkuri24 DFSORT/ICETOOL 8 Tue Oct 09, 2018 9:39 am
No new posts ISPF 3.4 edit of a ps file, disp use... Carrie Da Silva TSO/ISPF 3 Fri Sep 21, 2018 3:01 am
No new posts ICETOOL - Finding keys within a file nartcr DFSORT/ICETOOL 10 Mon Sep 17, 2018 6:39 am
No new posts Remove selected duplicate rows from t... hnharish DFSORT/ICETOOL 3 Wed Sep 05, 2018 3:11 am

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