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
 

 

Logic for to get old date,when multiple rows returned?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Thu Oct 29, 2009 6:23 pm    Post subject: Logic for to get old date,when multiple rows returned?
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 29, 2009 6:35 pm    Post subject:
Reply with quote

querying a vsam returning more rows ?

How can i get old? => just don't die.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Thu Oct 29, 2009 6:42 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 29, 2009 9:21 pm    Post subject:
Reply with quote

Hello,

Of the 3 records with the 26/10/2009 date, which do you consider "old"? Why do you consider "that one" the oldest. . . icon_confused.gif
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu Oct 29, 2009 9:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Fri Oct 30, 2009 9:34 am    Post subject:
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Fri Oct 30, 2009 10:03 am    Post subject:
Reply with quote

Please read/reread Robert's post concerning the proper terminology to use to get more meaningful responses.
Back to top
View user's profile Send private message
donevin

New User


Joined: 07 Jun 2005
Posts: 70
Location: South Africa

PostPosted: Fri Oct 30, 2009 3:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Fri Oct 30, 2009 3:11 pm    Post subject:
Reply with quote

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... icon_razz.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 30, 2009 3:18 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Fri Oct 30, 2009 3:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Fri Oct 30, 2009 3:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 30, 2009 3:31 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 30, 2009 4:07 pm    Post subject:
Reply with quote

and don't forget to test for EOF during loops icon_redface.gif
Back to top
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Fri Oct 30, 2009 4:50 pm    Post subject:
Reply with quote

GuyC wrote wrote:
I wasn't replying to your post, just giving a different way to solve the prob without an alternate index.

icon_redface.gif .. sorry for the misunderstanding...
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm


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