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
 
Match two files using multiple keys and write only 2nd file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
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    Post subject: Match two files using multiple keys and write only 2nd file
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

Moderator


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

PostPosted: Fri Aug 18, 2017 11:30 am    Post subject:
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    Post subject:
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

Moderator


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

PostPosted: Fri Aug 18, 2017 12:47 pm    Post subject:
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: 408
Location: USA

PostPosted: Fri Aug 18, 2017 8:40 pm    Post subject: Reply to: Match two files using multiple keys and write only 2nd file
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

Senior Member


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

PostPosted: Fri Aug 18, 2017 8:51 pm    Post subject:
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: 408
Location: USA

PostPosted: Fri Aug 18, 2017 8:58 pm    Post subject: Reply to: Match two files using multiple keys and write only 2nd file
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

Active User


Joined: 29 Apr 2008
Posts: 212
Location: Maryland

PostPosted: Fri Aug 18, 2017 11:15 pm    Post subject:
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    Post subject:
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

Active User


Joined: 29 Apr 2008
Posts: 212
Location: Maryland

PostPosted: Fri Aug 18, 2017 11:27 pm    Post subject:
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

Active User


Joined: 29 Apr 2008
Posts: 212
Location: Maryland

PostPosted: Fri Aug 18, 2017 11:32 pm    Post subject:
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: 210
Location: Chennai

PostPosted: Mon Aug 21, 2017 9:15 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm
No new posts Updating multiple counts in trailer!! Vignesh Sid SYNCSORT 4 Mon Nov 06, 2017 1:04 pm

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