|
View previous topic :: View next topic
|
| Author |
Message |
naveen katkuri
New User
Joined: 12 Mar 2018
Posts: 3
Location: Australia
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| 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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| 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  ................
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Cheers Arun,
I didn't think that I was going crazy(ier)  |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
expat - Glad you are back.
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
