IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetching the max date between 5 date colums


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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: 1281
Location: Belgium

PostPosted: Mon Feb 07, 2011 1:38 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Need help to append a date&tsp at... DFSORT/ICETOOL 9
No new posts Fetch data from programs execute (dat... DB2 3
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
Search our Forums:

Back to Top