View previous topic :: View next topic
|
Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006 Posts: 93
|
|
|
|
I have two input files and i need to compare 1st input file a/c no& amount with 2nd input file a.c no and amount.If match found then i want to write the 2nd input file record in to output file.If more than one match found i want to write the 2nd input file record which is having the oldest date record based date field in 2nd input file?
A/c no is pic x(6),Amount is pic x(12)v99 comp-3.
Please let me know thelogic for this requiremnt?
File -1 Data :-
---------
Command ===> This PS file
****** **********************
a/c no | amount
01035900000000232.50
03466500000000232.50
03466500000000232.50
03466500000000232.50
03466500000000232.50
03466700000000232.50
03466800000000232.50
03466900000000222.50
****** **********************
File-2 Data:- This VSAM file
--------------
Date action branch a/c num amount
20091027 1 999 010359 232.50
20091028 1 999 034665 232.50
20091029 1 999 034665 232.50
20091030 1 999 034665 232.50
20091030 1 999 034665 232.50
20091101 1 999 034667 232.50
20091102 1 999 034668 232.50
20091102 1 999 034669 222.50
Please let me know thelogic for this requiremnt? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well other than looking at the 'suggested logic' contained in the PROGRAMS button at the top of the page,
From the above input files, what is your desired output - as records, not your rules. |
|
Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006 Posts: 93
|
|
|
|
Desired out put for this requirement is
Date action branch a/c num amount
20091027 1 999 010359 232.50
20091028 1 999 034665 232.50
20091101 1 999 034667 232.50
20091102 1 999 034668 232.50
20091102 1 999 034669 222.50 |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what is the key for the vsam file? |
|
Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006 Posts: 93
|
|
|
|
VSAM file Key is a/c num&amount . |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Hi Babu,
My suggestion would be to SORT the file based on the a/c num, amount the DATE field before starting the actual processing.
In your code, after doing a read for each record in the second file, you could check the previous key value. If its the same, skip the record with no processing.
Code: |
1. SORT second file with a/c num, amount and date
2. Read each record in second file until EOF
2.1 If <key of current record> = <key of previous record>
2.1.1 Continue to next read ( Step 2 )
2.2 Find the key in the first file, If not found
2.2.1 Continue to next read ( Step 2 )
2.3 Write the record to the o/p
2.4 Update the <key of previous record> with <key of current record>
3. Exit
|
Do let me know in case of any clarifications / mistakes... |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
babu_hi wrote: |
VSAM file Key is a/c num&amount . |
Are you sure the key is a/c num and amt? From the Language Reference manual concerning indexed organization:
Quote: |
... the value in each prime key data item must be unique... |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Most places do not allow amounts to be part of the primary key. . . Many places to not allow amounts and/or quantities to be a key/index at all. . .
This sounds like a situation that might run for a long while until some customer repeats a purchase and winds up with the same account and $ and then the process will fail. And everyone will remark - "we haven't changed anything". |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Terry Heinze wrote: |
babu_hi wrote: |
VSAM file Key is a/c num&amount . |
Are you sure the key is a/c num and amt? From the Language Reference manual concerning indexed organization:
Quote: |
... the value in each prime key data item must be unique... |
|
... completely overlooked this point... |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
Your solution may work, Binop; I was just pointing out a VSAM rule. It's too late at night for me to think. |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Thanks Terry..
Terry Heinze wrote: |
I was just pointing out a VSAM rule. It's too late at night for me to think. |
Exactly my point... Not happy at myself because its morning for me.. and my thinking process should be at its peak... |
|
Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
Beside that, a prime key starts at position 1. And thats not the case here looking at the record lay-out of the output file.
But then it could be an alternate index, allowing duplicates. |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Hi Peter,
You said,
Quote: |
Beside that, a prime key starts at position 1. And thats not the case here looking at the record lay-out of the output file. |
A snapshot from the following link
Quote: |
VSAM indexed file organization
(Also referred to as VSAM KSDS (key-sequenced data set) organization.) In a VSAM indexed file (KSDS), the records are ordered according to the collating sequence of an embedded prime key field, which you define. The prime key consists of one or more consecutive characters in the records. The prime key uniquely identifies the record and determines the sequence in which it is accessed with respect to other records. A prime key for a record might be, for example, an employee number or an invoice number. |
Am I missing something... .. I couldn't find any reference telling the prime key should start at 1st byte... My understanding was that the VSAM key could start from any position and not necessarily the first byte in the record.. Please do correct me if I am wrong... |
|
Back to top |
|
|
belchoff
New User
Joined: 21 Oct 2009 Posts: 4 Location: UK
|
|
|
|
why dont you load one file into an array after sorting it, then read the other file, run a search in the table of the loaded file. |
|
Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
Binop,
my bad. Had another database organization in mind.
The default for vsam is a key of 64 bytes starting at pos 1 |
|
Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
Thanks Peter, for the clarification and info... |
|
Back to top |
|
|
|