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

Compare two files and update second file record on condition


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Sep 07, 2015 8:04 pm
Reply with quote

I have two input files F1 and F2, each having LRECL=2000 and RECFM=FB
Now I need to compare them and if records matches on keys from both files then we need to use condition: if the date field in F1 is older than date field in F2 by 365 days then overlay the first field of F2 with char 'D' else replace the F2 date with F1 date.
Basically I am not sure how to write the WHEN condition here in IFTHEN statement. Could anyone please help me out to write the correct statement.
Thanks
Key in both i/p file (2,3)
Date in both i/p file (5,8)
Note in F2 file, date will always be current date.

input file F1 :
Code:
 
' AAA20140506BBB...'
' BBB20140507XXX...'
' CCC20150101MMM...'

input file F2:
Code:

' AAA20150709BBB...'
' CCC20150709MMM...'

Output should be file F3,
Code:

'DAAA20150506BBB...'
' CCC20150101MMM...'


I am trying using SORT to implement this with below SYSIN card
Code:

//SYSIN DD *                                         
  SORT FIELDS=COPY                                   
  JOINKEYS FILES=F1,FIELDS=(2,3,A)                 
  JOINKEYS FILES=F2,FIELDS=(2,3,A)                 
  REFORMAT FIELDS=(F2:1,2000,?,F1:5,8)           
  OUTFIL IFOUTLEN=2000,                             
  IFTHEN=(WHEN=((5,8,CH,GT,(2002,8,sub,365))),OVERLAY=(1:C'D'),HIT=NEXT),                                 
  IFTHEN=(WHEN=((5,8,CH,LE,2002,8,sub,365)),OVERLAY=(5:2002,8)
/*                                                   
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Mon Sep 07, 2015 9:30 pm
Reply with quote

are the quotes part of your data?
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Mon Sep 07, 2015 10:04 pm
Reply with quote

No they are not part of data. I just put the data in quotes to show that there is one initial space in files. Data starts right after the starting quote.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Sep 08, 2015 2:51 am
Reply with quote

Change 'CH' to 'ZD' and then subtract.

If you subtract 20140506 from 20150709, what will you get? Is it 365 ? Nope, so the difference should say GE 10000.

And why HIT=NEXT ?

Try this:

Code:
   SORT FIELDS=COPY
   JOINKEYS FILES=F1,FIELDS=(2,3,A)
   JOINKEYS FILES=F2,FIELDS=(2,3,A)
   REFORMAT FIELDS=(F2:1,2000,F1:5,8)
   INREC IFTHEN=(WHEN=INIT,OVERLAY=(2010:5,8,ZD,SUB,2001,8,ZD)),
         IFTHEN=(WHEN=(2010,15,ZD,GE,10000),OVERLAY=(1:C'D')),
         IFTHEN=(WHEN=(2010,15,ZD,LT,10000),OVERLAY=(5:2001,8))
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Sep 08, 2015 12:57 pm
Reply with quote

Guys, you can't subtract dates like that :-)

DFSORT has date-arithmetic functions. Use those.
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Tue Sep 08, 2015 1:05 pm
Reply with quote

Hi Bill, Could you please suggest how we can use date function here. Thanks.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Sep 08, 2015 1:19 pm
Reply with quote

These are the functions:

Code:
ADDDAYS
ADDMONS
ADDYEARS
SUBDAYS
SUBMONS
SUBYEARS


So, you could add, or subtract, 365 days (or one year, depending on what you really want) to one of the dates, and then compare. CCYYMMDD is fine for comparison in that case.

You need to use syntax which exists, so look in the manual for how a WHEN=(logicalexpression) can be constructed. You can temporarily extend the record to include additional data (the amended date) and use that.

Since you just want the difference, DATEDIFF is even better. Perhaps. Think hard (ask) about what that 365 means. Is it literal, or is it "a year" or what?

There are other date functions as well. Take this opportunity to become aware of them.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Sep 08, 2015 8:13 pm
Reply with quote

Hello Bill,

Yes. I saw those functions in the guide earlier but never used them.

But now I am aware and know how to use. :-)

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

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Tue Sep 08, 2015 8:32 pm
Reply with quote

faizm,
Do you really want 365 days of 1 year difference? Run below job and observe the difference in output by yourself? Either way this should give you a very good idea on how to use the function itself.

Code:

//STEP001  EXEC PGM=SORT                       
//SORTIN   DD  *                               
2014010120130101                               
2013010120120101                               
2012010120110101                               
//SORTOUT  DD  SYSOUT=*                       
//SYSIN DD *                                   
   INREC OVERLAY=(31:1,8,Y4T,DATEDIFF,9,8,Y4T)
   SORT FIELDS=COPY                           
/*                                             
//SYSOUT DD SYSOUT=*                           


Thanks,
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 7
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