View previous topic :: View next topic
|
Author |
Message |
veeramanimurugesan
New User
Joined: 03 Nov 2009 Posts: 19 Location: chennai
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
|
|
|
|
You can write the code you want but I see the issue is with table normalization ... |
|
Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009 Posts: 293 Location: U.S.A.
|
|
|
|
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 |
|
|
veeramanimurugesan
New User
Joined: 03 Nov 2009 Posts: 19 Location: chennai
|
|
|
|
Thanks Ronald. |
|
Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
|
|
|
|
veeramanimurugesan
Post the results of the query for everybody's benefit. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
select key,max(col1,col2,col3,col4,col5) from table
works just fine. |
|
Back to top |
|
|
|