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
 

 

Fetching the max date between 5 date colums

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Sat Feb 05, 2011 1:45 pm    Post subject: Fetching the max date between 5 date colums
Reply with quote

Hi,

There are 5 different date columns in a table. My input will be account number. I go to select the row using account number in which i also need to select the date which is max of the 5 date columns for that account.

The maximum date which is fetched for that account will then be inserted/updated in another table.

I am storing those 5 dates in an array in COBOL and getting the max of them to move to another table.

But experts, can you please advise if there is any simpler or effective way of doing this? Thanks in advance.
Back to top
View user's profile Send private message

rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sat Feb 05, 2011 6:37 pm    Post subject: Reply to: Fetching the max date between 5 date colums
Reply with quote

You can write the code you want but I see the issue is with table normalization ...
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Sat Feb 05, 2011 10:49 pm    Post subject:
Reply with quote

Found this (untested) on the web - might be worth a try:
Code:
 select max(dateValue)
   from (select datecolumn1 as dateValue
          union all
         select datecolumn2
          union all
         select datecolumn3
          union all
         select datecolumn4
          union all
         select datecolumn5) as MaxDateValue
   from your_table
  where account_number = :acct-num;


Alternatively, you could use the LE function MAX in your COBOL program to select the max value of the 5 returned dates - e.g.
Code:
MOVE FUNCTION MAX(WSDATE1 WSDATE2 WSDATE3 WSDATE4 WSDATE5) TO WS-MAXDATE.
Back to top
View user's profile Send private message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Sat Feb 05, 2011 11:09 pm    Post subject:
Reply with quote

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

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sun Feb 06, 2011 12:28 am    Post subject: Reply to: Fetching the max date between 5 date colums
Reply with quote

veeramanimurugesan

Post the results of the query for everybody's benefit.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 07, 2011 1:38 pm    Post subject:
Reply with quote

select key,max(col1,col2,col3,col4,col5) from table
works just fine.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
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
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
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


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