IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Match two files using multiple keys and write only 2nd file


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
santoshks1987

New User


Joined: 18 May 2017
Posts: 3
Location: India

PostPosted: Fri Aug 18, 2017 10:50 am
Reply with quote

Hi,

I searched the forum, but could not find something specific to my requirement. So I am continuing to post the query.

I have two input files F1 of length 1000 and F2 of length 800. I have joined them on multiple keys.

I want to create a single output file with matched records from F2. While creating the output, I want check for a filed in F2, if it is blank I have to overwrite it with a value from F1. I am able to write F2 to output, but I am unable to overwrite the field in case of blank.

Please find my control card below,
Code:

JOINKEYS FILE=F1,FIELDS=(1,9,A, 
                         55,3,A,
                         10,5,A,
                         15,1,A,
                         105,8,A,
                         76,16,A,
                         92,12,A,
                         113,1,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A, 
                         13,3,A,
                         471,5,A,
                         476,1,A,
                         45,8,A,
                         29,16,A,
                         16,12,A,
                         54,1,A)
REFORMAT FIELDS=(F1:1,1000,F2:1,800)               
OPTION COPY                                       
OUTFIL FNAMES=SORTOUT,INCLUDE=(1092,12,CH,GE,C' '),
  BUILD=(1:1001,800),                             
IFTHEN=(WHEN=(1092,12,CH,EQ,C' '),                 
OVERLAY=(92:236,12))                               


I am creating a SORTOUT with LRECL=800.

Any help is appreciated. Thanks a lot.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri Aug 18, 2017 11:30 am
Reply with quote

Can you double check this?

Code:
OUTFIL FNAMES=SORTOUT,INCLUDE=(1092,12,CH,GE,C' '),
   BUILD=(1:1001,800),                             
 IFTHEN=(WHEN=(1092,12,CH,EQ,C' '),                 
 OVERLAY=(92:236,12)) 


I find OVERLAY to be strange is that correct?
Back to top
View user's profile Send private message
santoshks1987

New User


Joined: 18 May 2017
Posts: 3
Location: India

PostPosted: Fri Aug 18, 2017 12:10 pm
Reply with quote

Hi Pandora,
Thank you.
I don't think it is correct. JCL is not executing successfully.

I wanted to know this part exactly. How can I overwrite a part of output which is built from F2 with some data from F1 based on the condition.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri Aug 18, 2017 12:47 pm
Reply with quote

Looking with nak*** eyes your approach is correct wrt. to OVERLAY just check the condition
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Aug 18, 2017 8:40 pm
Reply with quote

Code:
OUTFIL FNAMES=SORTOUT,INCLUDE=(1092,12,CH,GE,C' '),
   BUILD=(1:1001,800),                             
 IFTHEN=(WHEN=(1092,12,CH,EQ,C' '),                 
 OVERLAY=(92:236,12)) 

If you are Including the records which have position 1092,12 greater than or equal to Spaces, then how would you use the condition 1092,12 equal to spaces in IFTHEN ? You've already filtered those records with INCLUDE that you are trying to get with IFTHEN.

Moreover, you've already built the Outfil record as 800 bytes. IFTHEN is Not going to work after you are already done with OUTFIL BUILD (this is 'usually' the last statement in a sort card). The IFTHEN here will give an error.

You'll need to remove the INCLUDE and BUILD and just run with OUTFIL IFTHEN as below (get the positions correct to build 1,800 bytes record or instead of OVERLAY, do a BUILD):
Code:
OUTFIL IFTHEN=(WHEN=(1092,12,CH,EQ,C' '),                 
 OVERLAY=(92:236,12))


.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Aug 18, 2017 8:51 pm
Reply with quote

Also to add to RahulG31, it don't make sense to overlay at 92 position when your BUILD don't need that.
What is the requirement?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Aug 18, 2017 8:58 pm
Reply with quote

Quote:
If you are Including the records which have position 1092,12 greater than or equal to Spaces, then how would you use the condition 1092,12 equal to spaces in IFTHEN ? You've already filtered those records with INCLUDE that you are trying to get with IFTHEN.

I realized that this is Not correct since GE includes Equal.

.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Aug 18, 2017 11:15 pm
Reply with quote

In OUTFIL statement, parameters BUILD, and IFTHEN=(...OVERLAY...) are incompatible at the same level.

Whenever IFTHEN is used, any of BUILD/OVERLAY are allowed only as sub-parameters of IFTHEN.
And vice versa: whenever separate BUILD is used, IFTHEN is not allowed in the same statement.

SYNCSORT should give you the message
Quote:
WER270A OUTFIL STATEMENT : DUPLICATE PARM FOUND

did it not appear?

In your case you must use two IFTHEN with different BUILD in each of them.
Back to top
View user's profile Send private message
santoshks1987

New User


Joined: 18 May 2017
Posts: 3
Location: India

PostPosted: Fri Aug 18, 2017 11:18 pm
Reply with quote

Hi Sergeyken,

Yes, I got the error as duplicate parm found.

I was able to solve it in 2 steps.

1. Write the matching file of length 1800 from both the files.
2. Create a subset from this file and do the overlay as well.

Thanks all for your inputs.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Aug 18, 2017 11:27 pm
Reply with quote

Two steps are not needed.
Just re-organize your OUTFIL (please verify my re-calculated offsets :-) ):
Code:
OUTFIL FNAMES=SORTOUT,
   INCLUDE=(1092,12,CH,GE,C' '),
   IFTHEN=(WHEN=(1092,12,CH,NE,C' '),                 
           BUILD=(1001,800)),     full F2
   IFTHEN=(WHEN=NONE,                 
           BUILD=(1001,91,        begin of F2
                  236,12,         replacement from F1
                  1103,697))      end of F2
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Aug 18, 2017 11:32 pm
Reply with quote

In order to simplify, and to clarify the idea, I would re-organize it further:
Code:
. . . . . . . .
REFORMAT FIELDS=(F2:1,800,      full F2
                 F1:92,12)      the only needed part of F1
OUTFIL FNAMES=SORTOUT,
    INCLUDE=(92,12,CH,GE,C' '),
    IFTHEN=(WHEN=(92,12,CH,NE,C' '),                 
            BUILD=(1,800)),     full F2
    IFTHEN=(WHEN=NONE,                 
            BUILD=(1,91,        begin of F2
                   801,12,      replacement from F1
                   103,697))    end of F2
END
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Aug 21, 2017 9:15 pm
Reply with quote

santoshks1987 wrote:
Hi Sergeyken,

Yes, I got the error as duplicate parm found.

I was able to solve it in 2 steps.

1. Write the matching file of length 1800 from both the files.
2. Create a subset from this file and do the overlay as well.

Thanks all for your inputs.


Can you share your two steps solution ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top