|
View previous topic :: View next topic
|
| Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
I have VSAM archive file that conatais last 5 days data.
the Vsam file layout and date is like this.
| Code: |
A/C No Amount capture-date Sequnce num
122 200 24/10/2009 32320
123 300 25/10/2009 32321
124 200 26/10/2009 32322
124 200 26/10/2009 32323
124 200 26/10/2009 32324
125 700 27/10/2009 32325
126 600 28/10/2009 32326
127 500 28/10/2009 32327
|
I will get A/C no and Amount from today's file and now i am querying VSAM Archiving file with the A/c and amount of Today's file.
Example: I am querying VSAM file with A/C No = 124 and Amount = 200 so it will return multiple rows,those are
| Code: |
A/C No Amount capture-date Sequnce num
124 200 26/10/2009 32322
124 200 26/10/2009 32323
124 200 26/10/2009 32324 |
from these rows i want select the old date record(based on Capture Date) then i want to update the VSAM record.
How can i get old?Please tell me the logic thru this post? |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
querying a vsam returning more rows ?
How can i get old? => just don't die. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Quote: |
| so it will return multiple rows |
No, it won't. VSAM files have RECORDS not ROWS. You cannot return a row from a VSAM file, ever. You cannot query a VSAM file -- you READ it.
| Quote: |
| from these rows i want select the old date record(based on Capture Date) |
If all three are the same date, how do you know which is the oldest?
| Quote: |
How can i get old?Please tell me the logic thru this post? |
It is not at all clear what you are wanting to do, and the data you have posted does not provide enough information to resolve your questions -- if that is all you have, then you're not likely to get much help since your terminology is not appropriate (rows and queries are associated with relational data bases, not VSAM files) and you've not laid out what you have to get the results you want. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Of the 3 records with the 26/10/2009 date, which do you consider "old"? Why do you consider "that one" the oldest. . .  |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| Quote: |
| How can i get old?Please tell me the logic thru this post? |
To get old, you just have to wait long enough.
Sorry about that. |
|
| Back to top |
|
|
babu_hi
New User
Joined: 11 Apr 2006
Posts: 93
|
|
|
|
Example: I am querying VSAM file with A/C No = 124 and Amount = 200 so it will return multiple rows,those are
Code:
A/C No Amount capture-date Sequnce num
124 200 27/10/2009 32322
124 200 25/10/2009 32323
124 200 28/10/2009 32324
Sorry i mentioned wrong dates in earlier post.When i read file with A/C No = 124 and Amount = 200 then 3 rows returned but i want to select the which is old date ie 124 200 25/10/2009 32323 .how can i do this in cobol-vsam? |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Please read/reread Robert's post concerning the proper terminology to use to get more meaningful responses. |
|
| Back to top |
|
|
donevin
New User
Joined: 07 Jun 2005
Posts: 70
Location: South Africa
|
|
|
|
The fastest way to solve this IMHO is to cast the dates like so . . .
| Code: |
01 OLDEST-DATE PIC 9(08).
01 COUNT PIC 9.
01 COMPARE-DATE-TABLE.
03 COMPARE-DATE PIC 9(08) OCCURS 6. (or however much you require)
PROCEDURE DIVISION.
*do the following for all dates
STRING DATE(7 : 4) DATE(4 : 2) DATE (1 : 2) DELIMITED BY SIZE INTO
COMPARE-DATE (ELEMENT-NO).
*now find the smallest one
MOVE COMPARE-DATE (1) TO OLDEST-DATE.
PERFORM VARYING COUNT FROM 1 BY 1 UNTIL COUNT > 6
IF COMPARE-DATE (COUNT) < OLDEST-DATE
MOVE COMPARE-DATE(COUNT) TO OLDEST-DATE
END-IF
END-PERFORM
|
Please feel free to suggest a better, more efficient way. |
|
| Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN
|
|
|
|
Hi,
I was also thinking of using Alternate Index. If we define the A/C No, Amount and the Date as the key, probably we could do a dynamic read with A/C No and Amount having the key and Low-values moved to Date.
This is only a suggestion... but i dont know which would be efficient...  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Something like this ?
| Code: |
Read next todayfile
move 'n' to ws-update
START oldfile with partial-todaykey
read next
perform until partial-oldkey<>partial-todaykey
if olddate < today-date (with whatever logic you test this)
move old-rec-fields to todayrec
move 'y' to ws-update
end-if
read next
end-perform
if ws-update = 'y'
rewrite todayrec
end-if |
|
|
| Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN
|
|
|
|
Hi Guy,
I am not very sure why we would need this part of the code..
| Code: |
perform until partial-oldkey<>partial-todaykey
if olddate < today-date (with whatever logic you test this)
move old-rec-fields to todayrec
move 'y' to ws-update
end-if
read next
end-perform |
If at all there is a record with the specific A/C no. and Amount it will be returned with the oldest date since we are moving LOW-VALUES to Date field. Only validation that would be required is to verify the A/C No. and the Amount if its the same...
Please do correct me if I am wrong... |
|
| Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN
|
|
|
|
Hi Guy,
I guess you have suggested a SEQUENTIAL read here... I was suggesting a DYNAMIC read by moving the key values from today-file and moving LOW-VALUES to the Date Field. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Binop B wrote: |
Hi Guy,
I guess you have suggested a SEQUENTIAL read here... I was suggesting a DYNAMIC read by moving the key values from today-file and moving LOW-VALUES to the Date Field. |
yes, I wasn't replying to your post, just giving a different way to solve the prob without an alternate index. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
and don't forget to test for EOF during loops  |
|
| Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN
|
|
|
|
| GuyC wrote wrote: |
| I wasn't replying to your post, just giving a different way to solve the prob without an alternate index. |
.. sorry for the misunderstanding... |
|
| Back to top |
|
|
|
|
