Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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 Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us