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: 397
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: 1788
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: 397
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: 175
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: 175
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: 175
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: 202
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 Compare yesterday's date to the one o... migusd SYNCSORT 8 Fri Sep 22, 2017 11:35 pm
No new posts Comparing 2 Files using Current time arunsoods DFSORT/ICETOOL 5 Fri Sep 22, 2017 6:00 pm
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm

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