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
 
Process Last Key of a record based on if else conditions

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
naveen katkuri

New User


Joined: 12 Mar 2018
Posts: 3
Location: Australia

PostPosted: Mon Nov 12, 2018 9:54 am    Post subject: Process Last Key of a record based on if else conditions
Reply with quote

Hello Experts,

I have an ACCOUNT_NO, REF_NO, REASON_CODE and VALID_DATE fields in the input file .

( Input file is already sorted on ACCOUNT_NO, REF_No and VALID_DATE fields.)

For each ACCOUNT_NO who's REFERENCE_NO is same and has multiple REASON CODE, then I should process the last record with REASON_CODE with highest VALID_DATE in the output,
If there is no REASON_CODE then simply write Highest VALID_DATE in the output file based on ACCOUNT_No AND REFERENCE_NO as shown below:

Code:
CCOUNT_NO    REF_NO   REASON_C     VALID_DATE
                                                       
----------    ------   ------   ----------
 11111111      1       NEW      2017-11-26
 11111111      1       DLET     2017-12-26 --> OUTPUT
 11111111      2       SQN      2018-01-28
 11111111      2       ROLL     2018-02-26 --> OUTPUT
 11111111      3       AMT      2018-03-26 --> OUTPUT
 11111111      3                9999-12-31
 11111111      4                9999-12-31 --> OUTPUT
 11111111      5                9999-12-31 --> OUTPUT
 22222222      1       SQN      2018-07-26
 22222222      1       DLET     2018-08-26
 22222222      1       AMT      2018-09-26
 22222222      1       RNEW     2018-10-28 --> OUTPUT
 22222222      1                9999-12-31
 22222222      2                9999-12-31 --> OUTPUT
 22222222      3                9999-12-31 --> OUTPUT
 33333333      2                9999-12-31 --> OUTPUT
 33333333      4                9999-12-31 --> OUTPUT
 44444444      2       SQN      2018-08-28
 44444444      2       AMT      2018-09-28
 44444444      2       CLO      2018-10-28 --> OUTPUT
 44444444      4                9999-12-31 --> OUTPUT


Final Output:


ACCOUNT_NO    REF NO   REASON_C   VALID DATE 
----------    ------   ------   ---------- 
 11111111      1       DLET     2017-12-26 
 11111111      2       ROLL     2018-02-26 
 11111111      3       AMT      2018-03-26 
 11111111      4                9999-12-31 
 11111111      5                9999-12-31 
 22222222      1       RNEW     2018-10-28 
 22222222      2                9999-12-31 
 22222222      3                9999-12-31 
 33333333      2                9999-12-31 
 33333333      4                9999-12-31 
 44444444      2       CLO      2018-10-28 
 44444444      4                9999-12-31 



However, the code which Im using is processing only the last records from the each of the group irrespective of the VALID_DATE, the main aim of the sort is to get the last closed accounts with REASON_CODE and current accounts(whos Valid date = 9999-12-31)


Code:
//TOOLIN   DD  *                                                     
  SELECT FROM(IN) TO(OUT) ON(2,06,PD) ON(7,4,BI) LAST USING(CTL1) 
/*                                                                   
//CTL1CNTL DD *                                                     
  INCLUDE COND=(11,4,CH,NE,C' ')                                     



So could you please let me know if I can use any other conditions to achieve the above requirement.

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

sergeyken

Active User


Joined: 29 Apr 2008
Posts: 388
Location: Maryland

PostPosted: Mon Nov 12, 2018 11:23 pm    Post subject: Re: Process Last Key of a record based on if else conditions
Reply with quote

naveen katkuri wrote:
However, the code which Im using is processing only the last records from the each of the group irrespective of the VALID_DATE, the main aim of the sort is to get the last closed accounts with REASON_CODE and current accounts(whos Valid date = 9999-12-31)
Code:
//TOOLIN   DD  *                                                     
  SELECT FROM(IN) TO(OUT) ON(2,06,PD) ON(7,4,BI) LAST USING(CTL1) 
/*                                                                   
//CTL1CNTL DD *                                                     
  INCLUDE COND=(11,4,CH,NE,C' ')                                     

So could you please let me know if I can use any other conditions to achieve the above requirement.

Why not to add extra selection?
Code:
//CTL1CNTL DD *                                                     
  INCLUDE COND=(11,4,CH,NE,C' ',
         AND,date_pos,10,CH,EQ,C'9999-12-31')                                     

date_pos is impossible to find from your description, but you personally must know it.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8744
Location: Welsh Wales

PostPosted: Tue Nov 13, 2018 12:41 pm    Post subject:
Reply with quote

Code:

//TOOLIN   DD  *                                                     
  SELECT FROM(IN) TO(OUT) ON(2,06,PD) ON(7,4,BI) LAST USING(CTL1) 
/*                                                                   
//CTL1CNTL DD *                                                     
  INCLUDE COND=(11,4,CH,NE,C' ')                                     

OK, it is a long long time since I've been allowed access to a mainframe so maybe this is a little off target ................... so if I'm wrong let me know

But you have a condition shown above that tests for 11,4,CH,NE,C' ' - and I can only guess that this relates to the field Reason_c as your data representation has nothing but C' ' in position 11 - so please learn to match your data samples with your code statements.

But I digress icon_eek.gif ................
To me that statement only includes all records without 4 C' ' at pos 11, but your output shows records with 4*C' ' included.

Perhaps I have not fully understood your request, or that you haven't made it idiot-proof simple to understand.

As I have said it is about four years since I have played on the mainframe, so maybe I am wrong.

I will also ask if the statements above have been tested by you, and if so what results did you get.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2448
Location: @my desk

PostPosted: Tue Nov 13, 2018 8:22 pm    Post subject:
Reply with quote

Hi expat,

Looks like the OP needs the blank reason code record in output when there are no non-blank reason codes for that Account+Reference number, (if there are non-blank reason code records, then get the one with the max-date). If he runs his card as is, he won't get such records in the output, so I think he is trying to get those into the output as well.

naveen katkuri,

For 'current accounts' will there be duplicates on Account+Reference number?
Or will it be just one record with a blank reason code and date='9999-12-31'?

sergeyken wrote:
Why not to add extra selection?
Code:
INCLUDE COND=(11,4,CH,NE,C' ',
         AND,date_pos,10,CH,EQ,C'9999-12-31') 
sergeyken,
This will return nothing. For non blank reason codes, the dates are NOT='9999-12-31'.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8744
Location: Welsh Wales

PostPosted: Tue Nov 13, 2018 9:00 pm    Post subject:
Reply with quote

Cheers Arun,

I didn't think that I was going crazy(ier) icon_biggrin.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2448
Location: @my desk

PostPosted: Wed Nov 14, 2018 12:51 am    Post subject:
Reply with quote

expat - Glad you are back.icon_smile.gif

naveen katkuri,

Fields(p,l) >> 2,6 and 7,4 are overlapping in your control statements, Is that a typo? If those are contiguous, you can probably combine into one 10-byte key.

Here is an example using a COPY operation that might help you.

The example assumes the input to be of FB/LRECL=80 and the key (account+reference) at pos-1, length-15 and the date field at pos-32 and length-10. The idea is to assign a sequence number to each record in the acct-reference group and then OMIT the 9999-12-31 records having sequence number > 1. Then use TRAILER3 to write the last record in each group. You can modify it as per your actual field positions and data set attributes.
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//STEP01 EXEC PGM=SORT                             
//SORTIN   DD *                                     
11111111      1       NEW      2017-11-26           
11111111      1       DLET     2017-12-26 --> OUTPUT
11111111      2       SQN      2018-01-28           
11111111      2       ROLL     2018-02-26 --> OUTPUT
11111111      3       AMT      2018-03-26 --> OUTPUT
11111111      3                9999-12-31           
11111111      4                9999-12-31 --> OUTPUT
11111111      5                9999-12-31 --> OUTPUT
22222222      1       SQN      2018-07-26           
22222222      1       DLET     2018-08-26           
22222222      1       AMT      2018-09-26           
22222222      1       RNEW     2018-10-28 --> OUTPUT
22222222      1                9999-12-31           
22222222      2                9999-12-31 --> OUTPUT
22222222      3                9999-12-31 --> OUTPUT
33333333      2                9999-12-31 --> OUTPUT
33333333      4                9999-12-31 --> OUTPUT
44444444      2       SQN      2018-08-28           
44444444      2       AMT      2018-09-28                   
44444444      2       CLO      2018-10-28 --> OUTPUT       
44444444      4                9999-12-31 --> OUTPUT       
//SYSOUT   DD SYSOUT=(A)                                   
//SORTOUT  DD SYSOUT=(A)                                   
//SYSIN    DD *                                             
  OPTION COPY                                               
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(81:SEQ=8))
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                                 
  OMIT=(32,10,CH,EQ,C'9999-12-31',AND,81,8,ZD,GT,1),       
  SECTIONS=(1,15,TRAILER3=(1,80))
SORTOUT
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
11111111      1       DLET     2017-12-26 --> OUTPUT                   
11111111      2       ROLL     2018-02-26 --> OUTPUT                   
11111111      3       AMT      2018-03-26 --> OUTPUT                   
11111111      4                9999-12-31 --> OUTPUT                   
11111111      5                9999-12-31 --> OUTPUT                   
22222222      1       RNEW     2018-10-28 --> OUTPUT                   
22222222      2                9999-12-31 --> OUTPUT                   
22222222      3                9999-12-31 --> OUTPUT                   
33333333      2                9999-12-31 --> OUTPUT                   
33333333      4                9999-12-31 --> OUTPUT                   
44444444      2       CLO      2018-10-28 --> OUTPUT                   
44444444      4                9999-12-31 --> OUTPUT
EDIT : changed from sort to a copy 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 -> 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 Problem with OVERLAY - BI on SMF Record Ghellar DFSORT/ICETOOL 4 Sat Dec 01, 2018 9:41 pm
No new posts Propagating value from nth record to ... ganasrinivasan DFSORT/ICETOOL 7 Fri Nov 30, 2018 8:31 am
No new posts Previous record x current record jackare SYNCSORT 1 Thu Nov 29, 2018 7:56 am
This topic is locked: you cannot edit posts or make replies. Get value from 2nd record and PUSH it... ashokakash JCL & VSAM 1 Wed Oct 17, 2018 11:41 am
No new posts Merge data from record pairs ashokakash DFSORT/ICETOOL 5 Wed Oct 17, 2018 11:38 am

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