Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Compare two files and update second file record on condition

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Compare two files and update second file record on condition
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: 1715
Location: UK

PostPosted: Mon Sep 07, 2015 9:30 pm    Post subject:
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    Post subject: Reply to: Compare two files and update second file record on condition
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: 331
Location: USA

PostPosted: Tue Sep 08, 2015 2:51 am    Post subject: Reply to: Compare two files and update second file record on condition
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Tue Sep 08, 2015 12:57 pm    Post subject: Reply to: Compare two files and update second file record on condition
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    Post subject: Reply to: Compare two files and update second file record on condition
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Tue Sep 08, 2015 1:19 pm    Post subject: Reply to: Compare two files and update second file record on condition
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: 331
Location: USA

PostPosted: Tue Sep 08, 2015 8:13 pm    Post subject: Reply to: Compare two files and update second file record on condition
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: 578
Location: USA

PostPosted: Tue Sep 08, 2015 8:32 pm    Post subject:
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    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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us